Extract the worksheet name with a formula it’s possible in Excel.
- Use the CELL function to return the full path, including the worksheet name.
- Then, use the TEXTAFTER function to extract only the worksheet name.
- If you don’t have Excel 365, there is another technique to extract the filename from the string.
The CELL function
The CELL function automatically returns file properties, such as the full path, worksheet name, and cell address. This function helps you get dynamic information about a cell or the workbook.
The CELL function is different from the INFO function. The INFO function returns details about the system, such as the Excel version or memory availability.
- To extract the worksheet name, use the “filename” option in the CELL function.
- ⚠️ Make sure the file is saved. If not, the CELL function returns nothing.
When saved, the function returns this information in the following order:
- Path of the file, here
C:\Log\ - File name between brackets
[Extract Worksheet name.xlsx] - Worksheet name
Tuesday
How to extract the worksheet name?
Next, you will isolate the worksheet name from the previous string. We must extract the substring after the ] character.
The TEXTAFTER function
The TEXTAFTER function is available exclusively in Excel 365 and Excel Online. It’s easy to use—just specify the character that comes before the substring you want to extract. Here’s the full formula:
For all the other Excel versions
If you’re not using Excel 365, there’s still a way to extract the text after the ‘ ] ‘. This alternative method uses a combination of functions:
- LEN to get the total length of the string
- SEARCH to locate the position of the ‘ ] ‘
- And finally, the RIGHT function to extract the desired substring
Both methods help you extract the worksheet name dynamically with a formula.
