< 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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar