In Excel it can be useful to display the worksheet name in a cell of that worksheet. For example, to use this name in the first row of a worksheet as the title of that worksheet. By default, Excel has no function to put the name of a worksheet in a cell, but with a smart combination of functions this is possible.
When searching the internet, the following 2 functions are usually displayed that can be used for this:
=MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1),1)+1,99)or
=TEXTAFTER(CELL("filename",A1),"]")where the last function is only suitable for Excel 365.
In practice, however, these functions can give incorrect results. This happens if one or more square brackets appear in the file location path name. After all, square brackets are simply allowed in a folder name, and this can also occur with files on SharePoint.
Therefore, it is better to use the following function, which does provide correct results in these cases:
=SUBSTITUTE(RIGHT(SUBSTITUTE(CELL("filename",A1),"]",REPT("]",99)),99),"]","")This function gives an error message if the file has not yet been saved and therefore does not have a file name and this function does not work in the online version of Excel.
Short explanation
The function CELL("filename",A1) displays the full path with the file name including the worksheet name. The name of the file is shown in square brackets, after which the worksheet name follows immediately.
Then, using the functions SUBSTITUTE and REPT, each square bracket is replaced by 99 square brackets, after which the last 99 characters are subsequently retrieved using RIGHT. This is a string consisting of a large number of square brackets followed by the worksheet name.
Finally, the remaining square brackets are removed again using SUBSTITUTE, and only the worksheet name remains.
Because a worksheet name can never be longer than 31 characters and because a square bracket cannot appear inside a worksheet name, this always works correctly.
A cell reference must be added to the CELL function (A1 in this case, but it may be another cell) to ensure that the sheet name of the current worksheet is displayed. If no cell reference is included, the sheet name of the last modified cell will be displayed, which can be a cell on another worksheet.
If you don't have an English Excel version, the 'filename' option cannot be chosen from the drop-down list while entering the CELL function. In Dutch, for example, this option is called 'bestandsnaam'. However, although the 'filename' option cannot be chosen in this version, this value can be entered. The 'filename' option works in any language version, while the local values such as 'bestandsnaam' only work in the local language version.
Questions / suggestions
Hopefully this article helped you to display the worksheet name in a cell. If you have any questions about this topic or suggestions for improvement, please post a comment below.
