Go Back

How to Get Sheet Name Only in Excel

When the Excel Workbook contains multiple worksheets and we want to use each worksheet name as the header in the worksheet without typing it again and again. This can be achieved by using a formula in a blank cell of each worksheet that returns the worksheet name only. The formula uses the Excel MID function along with CELL and FIND functions to get sheet name only.

Figure 1. Getting Sheet Name Only

Example

Suppose we have sales workbook that contains multiple worksheets. Each month’s sales data is kept in a separate sheet and it is named with month and year of sales, like January-2018, February-2018 etc. We want to get the name of the sheet in a blank cell, say cell A1, of every sheet to use as the header of that sheet. The following formula returns the sheet name only in cell A1, such as;

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

Figure 2. The Formula to Get Sheet Name Only

Working of the Formula

The CELL function with the info_type “filename” returns the full path, file name, and current sheet name when used in a cell of the saved workbook, such as;

=CELL("filename",A1)

Figure 3. The CELL Function Result in Formula

The FIND function locates the position of the closing square bracket “]” character within the full path, file name, and current sheet name returned by the CELL function. We add 1 in the count to move the position by one character, such as;

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

And the MID function returns the portion of the text string starting from the position number returned by the FIND function as per the specified number of characters (31) as we cannot name a sheet longer than 31 characters.

Figure 4. Final Preview of the Formula

Instant Connection to an Expert through our Excelchat Service:

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

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