Go Back

Get sheet name only

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

Formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Explanation

CELL function returns the full file path, file name, and sheet name. This part of the formula:

CELL(“filename”,A1)

which results in something like this:

Filepath[workbook.xlsx]sheetname

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:

FIND(“]”,CELL(“filename”,A1))+1

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.

Example

In the photo above, I name the currently active sheet as HOW TO GET SHEET NAME ONLY.

Plug in the formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

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:

CELL(“filename”,A1)

Shall return the filepath[workbook.xlsx]HOW TO GET SHEET NAME ONLY

Figure 2. The return value of CELL(“filename”, A1)

The second argument:

FIND(“]”,CELL(“filename”,A1))+1

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.

Note

  • This formula only works with a workbook that is already saved. Without the file path, the formula won’t work.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need a complex formula and request a expert assistance. Only experts please as this require some thinking and lot of work. I need to get sheet name from the excel file.
Solved by X. L. in 20 mins
I would like to create a function that will get only the last name on a certain group of name with a complete LN, FN & MN.
Solved by G. H. in 13 mins

Leave a Comment

avatar