Add the Worksheet Name as a Title

December 31, 2021 - by Bill Jelen

Problem: I have 12 worksheets, labeled January through December. Is there a formula that will put a worksheet name in a cell?

Strategy: You can parse the sheet name from the CELL function.

The CELL function can return a variety of information about the top-left cell in a reference. =CELL(“Col”,A1) will tell you that A1 is in column 1. For this particular problem, =CELL(“FileName”,A1) will return the path, filename, and worksheet name of a saved workbook, as shown in cell A1 below.

An ancient function of =CELL("FileName",A1) returns the complete path, workbook name in square brackets, and then the sheet name. By identifying where the square brackets occur, you can isolate either the Sheet Name, Workbook Name, or Path. — Figure 245. CELL returns the path, filename and worksheet name.

To isolate the sheet name, you look for the right square bracket by using the FIND function. Then you use that location plus 1 as the start position for the MID function.

=MID(CELL(“FileName”,A1),FIND(“]”,CELL(“FileName”,A1)+1,25)

returns the worksheet name. Note that the final 25 argument is any number large enough to handle the longest sheet name you’ve used.

Additional Details: If you need to insert just the worksheet path in a cell, you can use =INFO(“Directory”) instead of trying to parse it from the CELL function.

Gotcha: The INFO function used to be able to return several bits of information about memory available, total memory, and so on. These results have not been correct since Windows XP. Today, Excel will return #N/A if you use the INFO function to return available memory.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ray Hennessy on Unsplash