How to Get the Sheet Name in Excel (2 Methods)

Method 1 – Using Excel Formula

Excel lacks a built-in function to directly retrieve the sheet name, but we can create a formula using the MID, CELL, and FIND functions.
Enter the below formula in any cell within your worksheet:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)

In this example, we named our worksheet Formula and then entered the formula in cell C5.

The CELL function provides the full path, workbook name, and current sheet name.

=CELL("filename",A1)

The FIND function locates the position of the right bracket (“]”) in the workbook name.

=FIND("]",CELL("filename",A1))+1

The MID function extracts the sheet name.
Arguments:
- 1st argument: =CELL(“filename”,A1) used in the first step.
- 2nd argument: =FIND(“]”,CELL(“filename”,A1))+1 used in the second step.
- 3rd argument: 31 which is the maximum length of a worksheet name in Excel.

Alternatively, you can use the below formula with the RIGHT function instead of the MID function:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

To get the active sheet name using VBA, use this simple code:

Sub SheetName()
Range("A1") = ActiveSheet.Name
End Sub

To find a sheet name based on its index number (useful for multiple worksheets), use the below code.
For example, we are in the second worksheet named vba1. With the following code, we can find the first sheet name which is Formula.

Sub Sheet Name()
Range("C5") = Sheets(1).Name
End Sub

With the following code, we can find out the last sheet name of a workbook. In this example, the last sheet name is vba2.

Sub SheetName()
Range("C6") = Sheets(Sheets.Count).Name
End Sub

Remember, you can also use MsgBox to display VBA code results if you don’t need to store them in a cell.