Get Sheet name only

In this article, we will learn how to get sheet name only in Excel.

In simple words, If you working with complicated data. And you want your sheet name as text in cell.

Cell function in Excel gets you the information regarding worksheet like col, contents, filename, ..etc.
Use the formula

=CELL(“filename”,A1)

“filename” : gets the full name of the sheet of the reference cell
A1 : Sheet’s cell reference
23
But we need to extract just the sheet name. Basically the last name.

As you can see the sheet name starts after ] (closed big bracket sign). For that we just needs its position in the text and then we will extract the sheet name.
24
Here I counted the characters from the last of the result which was 12. But we cannot do this every time.

So for extracting the sheet name from the full name, we need to find the position of the ] (closed big bracket sign)

Use the formula to find ]

=FIND(“]”,CELL(“filename”,A1))

25
58 is the position of <strong] .

Now we our formula is completed now

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

Explanation:
CELL(“filename”,A1) : Gets you the full name of the worksheet
FIND(“]”,CELL(“filename”,A1))+1 : this function gets you the position of ] and add +1 because we need the position of first char of only sheet name.
255 : Max word limit of sheet name by Excel.
MID : MID function extracts the specific substring from the text using the position from start to end.
26
Here we extracted the filename using the formula.

Now we have one more formula, instead of using MID function. Use the RIGHT function.
RIGHT function extracts the specific substring from the last of the text.
Use the formula:

=RIGHT(CELL(“filename”,A2),LEN(CELL(“filename”,A2))-FIND(“]”, CELL(“filename”,A2)))

Explanation:
FIND(“]”, CELL(“filename”,A2)) : Extracts the position of sheet name char.
LEN(CELL(“filename”,A2)) : gets the length of the text(filename)
RIGHT : RIGHT function extracts the string from the text using its position.
27
As you can see, this function works fine too. Yesss…Now we have 2 different formula with logic used to extract only the sheet name.

Get only the sheet name using VBA in Excel

Hope you understood How to get only sheet name of the worksheet in Excel 2016. Find more articles on Excel TEXT functions here. Please share your query below in the comment box. We will assist you.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube