Excel Formula – Get Worksheet Name

Use this Excel formula to get the worksheet name

Get Worksheet Name – Excel Formula

To calculate the worksheet name in one Excel Formula, use the following formula:
=mid(cell("filename",a1),find("]",cell("filename",a1))+1,999)
Notice in the image above this formula returns sheet names GetWorksheetName and Sheet3.

This code may look intimidating at first, but it’s less confusing if you split it out into separate formulas:

Excel Functions – Worksheet Name

The CELL Function:

The Cell Function returns information about a cell. Use the criteria “filename” to return the file location, name, and current sheet.
=cell("filename",a1)
Returns: path[workbook.xlsx]sheet : C:[workbook.xlsm]Sheet1 in example above.

The FIND Function:

The CELL Function returns [workbook.xlsx]sheet , but we only want the sheet name, so we need to extract it from the result. First though, we need to use the FIND Function to identify the location of the sheet name from the result.
=find("]",E5)
Returns: The location of the "]" character. 18 in example above.

The MID Function

Next, we will extract the desired text using the MID Function with the result of the FIND Function (+1) as the start_num.
=MID(E5,E6+1,999)
Returns: The sheet name: Sheet1 in example above.
Why did choose 999 for the num_characters input in the MID Function?  999 is a large number that will return all remaining characters. You could have chosen any other significantly large number instead.

Get Sheet Name in VBA

If you want to use VBA instead of an Excel Formula, you have many options.  This is just one example:
activesheet.range("a1").value = activesheet.name

Enter the current worksheet name in cell A1 using VBA.