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