Cell Function Returns Sheet Name, Workbook Name and Path





We have a useful function in excel called as the CELL function which can be modified to return the sheet name, workbook name and the file path.  Lets see how this is done.

Considering that we want this info for the current file.  First lets understand the CELL Function.  The syntax is =CELL(info_type, [reference]).  The CELL Function will return information about the formatting, location or contents of the 1st cell in the reference.  The info_type can be the filename, address, color, etc.  There is an available list of options in the dropdown which appears so you can choose the required one.  The reference is the current workbook which you are using.
 
img1
 
The file in this example is located at “B:\”. To obtain the file path, workbook name and sheet name , we use
 
img2
 
This will give us the output as B:\[Book1.xlsm]Sheet2 which is the file path + workbook name + sheet name of the workbook in this example.

Now lets move one step ahead.  To get the file path only from B:\[Book1.xlsm]Sheet2, we use
 
img3
 
=MID(CELL(“filename”),1,FIND(“[“,CELL(“filename”))-1)

The highlighted section will be evaluated first which will find the location of the opening box bracket “[“ in the function. It finds it as location 4.

Our function then narrows down to =MID(CELL(“filename”),1,3).  We need the characters starting from the 1st character to the 3rd character (4-1=3) using the mid function.  So the output we get will be the file path without the workbook name.   And we get the result as “B:\”

Now lets derive the workbook name.  Our function is
 
img4
 
We need to derive the workbook name from this result B:\[Book1.xlsm]Sheet2. Lets understand how it works.

=MID(CELL(“filename”),,FIND(“[",CELL("filename"))+1,(FIND("]“,CELL(“filename”)))-FIND(“[“,CELL(“filename”))-1)

The highlighted portion will be evaluated first.  The workbook name is always in box brackets so we have to search for the opening box bracket [ and closing box bracket ] and our result is within these 2.  So this part of the function will return the starting position of the text.

=MID(CELL(“filename”),FIND(“[",CELL("filename"))+1,(FIND("]“,CELL(“filename”)))-FIND(“[“,CELL(“filename”))-1)

This highlighted portion will evaluate to the last parameter of the MID function which is the number of characters to be extracted from the starting position.  So it calculates the position of the ending box bracket which is 15 in this case and deducts the position of the starting box bracket which is 4 and another -1 to exclude the starting box bracket.  So the mid function is complete as =MID(CELL(“filename”),5,10) and we get the result as Book1.xlsm as per our example.

Now lets derive the last aspect which is the sheet name.  Our function is
 
img5
 
It’s a very simple function.  First, the highlighted section in this function is evaluated - =MID(CELL(“filename”),,FIND(“]”,CELL(“filename”))+1,255)

This will give us the positing of the ending box bracket + 1 (to exclude the box bracket.  So in our example, this results in 15+1 = 16.  We directly arrive at the MID function now.  It looks like

=MID(CELL(“filename”),16,255).  We are asking the function to give us 255 (which is the maximum characters a cell can hold) characters starting from the 16th character of the filename.

So the result we get is Sheet2 as in this example.

To conclude, we can use the same base function =CELL(“filename”) with some enhancements to derive the information we require about the file name.

 



13 thoughts on “Cell Function Returns Sheet Name, Workbook Name and Path

  1. The formula listed here: =MID(CELL(“filename”),1,FIND(“[",CELL("filename"))+1, FIND("]“,(CELL(“filename”))-FIND(“[“,CELL(“filename”))-1) seems to be missing a closing parenthesis.

  2. The following function works correctly to return the workbook name: =MID(CELL(“filename”),FIND(“[",CELL("filename"))+1,(FIND("]“,CELL(“filename”))+1)-FIND(“[“,CELL(“filename”))-2)

  3. the function =MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,255) seems to be a global function. Putting multiple instaces of this function on separte sheets, always returns the same name

  4. I can’t get this formula to work, but this formula has 8 functions in the argument, I thought you could only have 7 functions in the formula.

  5. “the function =MID(CELL(“”filename””),FIND(“”]””,CELL(“”filename””))+1,255) returns the same value on each sheet unless you add a cell reference after “”filename””.
    the formula should look like this when done:
    =MID(CELL(“”filename””,b1),FIND(“”]””,CELL(“”filename””,b1))+1,255)
    By adding a cell reference after filename, the value returned should be the worksheet name of the proper worksheet and not the same value for all worksheets.”

  6. The formula listed here: =MID(CELL(“filename”),1,FIND(“[",CELL("filename"))+1, FIND("]“,(CELL(“filename”))-FIND(“[“,CELL(“filename”))-1) seems to be missing a closing parenthesis.

  7. “The following function works correctly to return the workbook name:
    =MID(CELL(“”filename””),FIND(“”["",CELL(""filename""))+1,(FIND(""]“”,CELL(“”filename””))+1)-FIND(“”[“”,CELL(“”filename””))-2)”

  8. the function =MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,255) seems to be a global function. Putting multiple instaces of this function on separte sheets, always returns the same name

  9. I can’t get this formula to work, but this formula has 8 functions in the argument, I thought you could only have 7 functions in the formula.

  10. “the function =MID(CELL(“”filename””),FIND(“”]””,CELL(“”filename””))+1,255) returns the same value on each sheet unless you add a cell reference after “”filename””.

    the formula should look like this when done:
    =MID(CELL(“”filename””,b1),FIND(“”]””,CELL(“”filename””,b1))+1,255)

    By adding a cell reference after filename, the value returned should be the worksheet name of the proper worksheet and not the same value for all worksheets.”

  11. “Thank you for your information, I found it very useful. There is small error in this formula. “”Worksheet function that returns the workbook name:””

    The corrected one should read as follow

    =MID(CELL(“”filename””),FIND(“”["",CELL(""filename""))+1,FIND(""]“”,CELL(“”filename””))-FIND(“”[“”,CELL(“”filename””))-1) “

Leave a Reply

Your email address will not be published. Required fields are marked *


nine − = 7

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>