Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles WORKBOOK How to Get Sheet Name Only in Excel

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.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc