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.
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:
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:
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.
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
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.