Get sheet name only

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.

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.