Explanation
In this example, the goal is to return the name of the current worksheet (i.e. tab) in the current workbook with a formula. This is a simple problem in the latest version of Excel, which provides the TEXTAFTER function . In older versions of Excel, you can use an alternative formula based on the MID and FIND functions. Both formula options rely on the CELL function to get a full path to the current workbook. Read below for a full explanation.
You must save your workbook before the formulas presented here will work since they depend on the CELL function to return a full path to the workbook.
Workbook path
The first step in this problem is to get the workbook path, which includes the workbook and worksheet name. This can be done with the CELL function like this:
CELL("filename",A1)
With the info_type argument set to “filename”, and reference set to cell A1 in the current worksheet, the result from CELL is a full path as a text string like this:
"C:\path\to\folder\[workbook.xlsx]sheetname"
Notice the sheet name begins after the closing square bracket ("]"). The problem now becomes how to extract the sheet name from the path. The best way to do this depends on your Excel version. Use the TEXTAFTER function if available. Otherwise, use the MID and FIND functions as explained below.
TEXTAFTER function
In Excel 365 , the easiest option is to use the TEXTAFTER function with the CELL function like this:
=TEXTAFTER(CELL("filename",A1),"]")
The CELL function returns the full path to the current workbook as explained above, and this text string is delivered to TEXTAFTER as the text argument. Delimiter is set to “]” in order to retrieve only text that occurs after the closing square bracket ("]"). In the example shown, the final result is “September” the name of the current worksheet in the workbook shown.
MID + FIND function
In older versions of Excel that do not offer the TEXTAFTER function, you can use the MID function with the FIND function to extract the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
The core of this formula is the MID function, which is used to extract text starting at a specific position in a text string. Working from the inside out, the first CELL function returns the full path to the current workbook to the MID function as the text argument:
CELL("filename",A1) // get full path
We then need to tell MID where to start extracting text. To do this, we use the FIND function with a second call to the CELL function to locate the “]” character. We want MID to start extracting text after the “]” character, so we use the FIND function to get the position, then add 1:
FIND("]",CELL("filename",A1))+1 // get start number
The result from the above snippet is returned to the MID function as start_num . For the num_chars argument, we hard-code the number 255*. The MID function doesn’t care if the number of characters requested is larger than the length of the remaining text, it simply extracts all remaining text. The final result is “September” the name of the current worksheet in the workbook shown.
*Note: In Excel user interface, you can’t name a worksheet longer than 31 characters, but the file format itself permits worksheet names up to 255 characters, so this ensures the entire name is retrieved.
Explanation
In this example, the goal is to get a normal path to the current workbook, without a sheet name, and without the square brackets ("[ ]") that normally enclose the workbook name. This is a pretty simple problem in the latest version of Excel, which provides the TEXTBEFORE function . In older versions of Excel, you can use a more complicated formula based on the LEFT and FIND functions. Both options use the CELL function to get a full path to the current workbook. Read below for a full explanation.
Get workbook path
The first step in this problem is to get the workbook path, which includes the workbook and worksheet name. This can be done with the CELL function like this:
CELL("filename",A1)
With the info_type argument set to “filename”, and reference set to cell A1 in the current worksheet, the result from CELL will be a full path as a text string like this:
"C:\path\to\folder\[workbook.xlsx]sheetname"
Notice the workbook name is enclosed in square brackets ("[name]"). This is close to what we want, but there are still two tasks that remain:
- Remove the sheet name
- Remove the square brackets ("[ ]")
The best way to do this depends on what Excel version you have. If you have the latest version of Excel, you should use a formula based on the TEXTBEFORE function. Otherwise, you can use the LEFT and FIND functions as explained below.
TEXTBEFORE option
In the worksheet shown above, the formula in E5 is:
=SUBSTITUTE(TEXTBEFORE(CELL("filename",A1),"]"),"[","")
This is an example of nesting . The CELL function is nested inside the TEXTBEFORE function, which is nested inside the SUBSTITUTE function. Working from the inside out:
- The CELL function returns the full path as a text string, as explained above.
- The TEXTBEFORE function returns all text before the closing square bracket ("]").
- The SUBSTITUTE function replaces the opening square bracket ("[") with an empty string ("").
The final result is a path to the workbook like this:
C:\path\to\folder\workbook.xlsx
Legacy Excel
In older versions of Excel without the TEXTBEFORE function, you can use a formula based on LEFT, CELL, FIND, and SUBSTITUTE:
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")
At a high level, this formula works in 4 steps:
- Get the full path and filename
- Locate the closing square bracket ("]")
- Remove sheet name and “]”
- Remove the opening square bracket ("]")
Note: the CELL function is called twice in the formula because we need the path twice, once for the FIND function to locate the “]”, and once for the SUBSTITUTE function to remove the “]”. CELL is a volatile function and can cause performance problems in larger or more complicated worksheets.
Get path and filename
To get the path and file name, we use the CELL function like this:
CELL("filename",A1) // get path and filename
The info_type argument is “filename” and reference is A1. The cell reference is arbitrary and can be any cell in the worksheet. The result is a full path like this as text:
C:\examples\[workbook.xlsx]Sheet1
Note the sheet name(“Sheet1”) appears at the end.
Locate the closing square bracket
The location of the closing square bracket ("]") is calculated like this
FIND("]",CELL("filename",A1))-1 // returns 26
The FIND function returns the location of “]” (27) from which 1 is subtracted to get 26. We subtract 1 because we want to remove text starting with the “]” that follows the filename.
Remove sheet name
In the previous step, we located the “]” at character 27, then stepped back to 26. This number is returned directly to the LEFT function as the num_chars argument. The text argument is again provided by the CELL function :
LEFT("C:\examples\[workbook.xlsx]Sheet1",26)
The LEFT function returns the first 26 characters of text .
C:\examples\[workbook.xlsx
At this point, LEFT has removed the sheet name, but notice the opening square bracket “[” remains.
Remove opening square bracket
The result from LEFT is returned to the SUBSTITUTE function as the text argument:
=SUBSTITUTE("C:\examples\[workbook.xlsx","[","")
SUBSTITUTE is configured to remove the opening square bracket by setting old_text to “[” and new_text to an empty string (""). The final result returned by SUBSTITUTE is:
C:\examples\workbook.xlsx
