Get Worksheet Name – Excel Formula
To calculate the worksheet name in one Excel Formula, use the following formula:
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:
The CELL Function:
The Cell Function returns information about a cell. Use the criteria “filename” to return the file location, name, and current sheet.
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.
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.
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
Return to Formula Examples