Being able to extract the current sheet name is often handy. Sheet names have to be unique, so you can name your sheets after departments or divisions. Excel will stop you duplicating a sheet name.
If you want the ability the extract the current sheet name via a built-in formula there are at least two ways to do it.
The first is one long, standalone formula – see below.
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))
This formula can be placed in any cell – even A1.
The second technique breaks the above formula into two parts, with two shorter formula.
In cell A1 you can use
=CELL("filename",A1)
In another cell you can use
=RIGHT($A$1,LEN($A$1)-SEARCH("]",$A$1))
In this way, two short formulas achieve the same result as one long formula.
The SEARCH function returns the character position of the text being searched for within another text string.
The LEN function returns the length of a string.
The RIGHT function extracts characters from the right of a text string.
The CELL function has a number of options to display. The “filename” option is by far the most commonly used. The image below shows the other options.
Always make sure you include a cell reference as the second argument in the CELL function. If you don’t you run the risk of having an incorrect sheet name in the cell. As you can see in the image below you can refer to the cell with the formula and you won’t get a circular reference warning.
Both examples only work once the file is saved. If you try this on Book1 before it is saved it will display an error up until you save the file.
Please note: I reserve the right to delete comments that are offensive or off-topic.