In Excel, to reference the name of the currently active sheet in any cell, you can use a rather long formula in this tutorial containing MID function and FIND function to do so.
Get Sheet Name Only
CELL function returns the full file path, file name, and sheet name. This part of the formula:
which results in something like this:
The part above is then fed into the MID function which is used to extract only the sheet name.
In order to let the MID function know where to start extracting, the part below is used to do just that job:
The FIND function gives us the exact position of the closed bracket “]”. The “+1” part is to get the position of the first character belongs to the sheet name.
The last argument of the MID function is 255. It is supplied to the function only extracts 255 characters of the sheet name since the limit of worksheet name is 255 characters.
In the photo above, I name the currently active sheet as HOW TO GET SHEET NAME ONLY.
Plug in the formula:
In any cell and you will get the result just like in figure 1.
In order to understand the logic behind the formula, let’s take a closer look at each individual argument of the MID function.
The first argument:
Shall return the filepath[workbook.xlsx]HOW TO GET SHEET NAME ONLY
Figure 2. The return value of CELL(“filename”, A1)
The second argument:
It returns a value of 40. As the first character of the sheet name in figure 2 is in the 40th position.
Figure 3. Return value of FIND(“]”,CELL(“filename”,A1)+1
The last argument is already explained in the Explanation.
- This formula only works with a workbook that is already saved. Without the file path, the formula won’t work.