How to Find Excel Sheet Name Using Formula (3 Examples)

In a Microsoft Excel file usually, we get the sheet name from the bottom of each sheet. But there are other options to find sheet names. We will discuss how to find a sheet name using an Excel Formula in this article.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

3 Formula Examples to Find Sheet Name in Excel

We will discuss some Excel formulas to search the sheet names in Excel. The following data set will be used for this.

1. Excel Formula Combining MID, CELL, and FIND Functions to Get Sheet Name

The MID function results in a certain number of letters from a given position of the text reference.

The CELL function gives information about a cell. Like formatting, contents, location, etc.

The FIND function searches a specific text from another text group.

We will apply a formula based on the MID, CELL, and FIND functions to find the sheet name in Excel.

Step 1:

• First, we added a row to view the name of the sheet.
• Then, go to Cell D11.
• Put the following formula on that cell.

Step 2:

• Press the Enter button and drag the Fill Handle icon.

We get the sheet name by this formula.

Formula Breakdown:

• CELL(“filename”)

We get the full path, excel file name, and present sheet name by this formula.

Result:  C:\Users\Alok\Desktop\[Find Sheet Name in Excel Formula.xlsm]Sheet2

• FIND(“]”,CELL(“filename”))+1

This Find function searches the location of the sign “]” (right bracket) from the previous result. Our sheet name is located after the right bracket. For that, we sum 1 with the value.

Result: 62

• MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,31)

In this formula, we sum 31 at the end, which indicates the maximum length of the worksheet name. We can use up to 255 here.

Result: Sheet2

Read More: How to Use Formula to Find Bold Text in Excel

2. Excel Formula Joining RIGHT, LEN, CELL, and FIND Functions to Get Worksheet Name

The LEN function provides the number of characters of a given text.

The RIGHT function returns the characters from the last or the rightmost side of a given text.

We will apply a combination of RIGHT, CELL, LEN, FIND functions to get the sheet name.

Step 1:

• Go to Cell D11 and put the following formula on that cell.

Step 2:

• Press the Enter button and pull the Fill Handle towards the last cell.

Like the previous, this formula finds the sheet name exactly.

Formula Breakdown:

• CELL(“filename”)

We get the full path, file name, and present sheet name by applying this formula.

Result:  C:\Users\Alok\Desktop\[Find Sheet Name in Excel Formula.xlsm]Sheet3

• LEN(CELL(“filename”))

This formula provides the length of the previous result.

Result: 67

• FIND(“]”,CELL(“filename”))

This Find function searches the location of the sign “]” (right bracket) from the previous result.

Result: 61

• LEN(CELL(“filename”))-FIND(“]”,CELL(“filename”))

Here a subtraction operation is performed between the last two formulas.

Result: 6

• RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND(“]”,CELL(“filename”)))

We will get 6 characters from the right side of the CELL(“filename”) formula.

Result: Sheet3

Read More: How to Find Last Cell with Value in Column in Excel

Similar Readings

• How to Find * Character Not as Wildcard in Excel (2 Methods)
• How to Find First Value Greater Than in Excel (4 Ways)
• Excel Find Last Column With Data (4 Quick Ways)
• How to Find from Right in Excel (6 Methods)
• Find First Occurrence of a Value in a Range in Excel (3 Ways)

3. Get All Sheet Names Using Excel INDEX Function

The INDEX function provides a value based on a given value from a table or range in Excel.

We will use the Define Name with a formula based on the INDEX function to get the sheet name in Excel.

Step 1:

• Go to the Define Name group from the Formulas tab.
• The New Name window will appear.
• Set a name in the Name box.
• Put the following formula on Refers to box and then press OK.

Step 2:

• Add two new columns to the dataset.

The Sheet Name column will view the sheet names. The Index column will indicate the serial number of sheets and will use in the formula as a reference.

Step 3:

• Go to Cell G5 and put the following formula.

Step 4:

• Now, hit the Enter button and pull the Fill Handle icon.

We get all the sheet names by applying this method.

2 Visual Basic Codes to Find Excel Sheet Name

We have learned how to find sheet names using Excel formulas so far. We can do the same thing using Excel VBA. Now in this section, we will use VBA codes to find the sheet name.

1. Get Excel Sheet Name Using a Custom VBA Function

We can use a custom VBA function to get the sheet name in Excel.

Step 1:

• Go to the bottom of the sheet where the sheet name is visible.
• Press the right button of the mouse.
• Choose the View Code option from the list.

Step 2:

• The Excel VBA command module will appear. We will write VBA code and run it from this module.

Step 3:

• Copy and paste the following VBA code into the command module.

Function Sheet_Name()
Sheet_Name = ActiveSheet.Name
End Function

Step 4:

• Save the code. Then go to Cell D11 of the dataset.
• Put the following formula on that cell.

Step 5:

• Now, press the Enter button.

Here, we get the sheet name by using the custom VBA function.

2. Find Excel Sheet Name Applying VBA ActiveSheet Property

We can use the ActiveSheet property of Excel VBA to get the sheet name.

Step 1:

• Press Alt+F11 to enter the VBA command module.
• Copy and paste the VBA code below.

Sub SheetName()
Dim sheet_name As String
sheet_name = ActiveSheet.Name
MsgBox "The name of the Sheet is: " & sheet_name
End Sub

Step 2:

• Hit F5 to run the code.

The sheet name is shown after running the VBA code.

Conclusion

In this article, we described how to find the sheet name using formulas in Excel. We also added the VBA code to get the sheet name. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• Excel Function: FIND vs SEARCH (A Comparative Analysis)
• How to Find Max Value in Range with Excel Formula (5 Easy Methods)
• FIND Function Not Working in Excel (4 Reasons with Solutions)
• How to Find Last Non Blank Cell in Row in Excel (5 Methods)
• [Solved!] CTRL+F Not Working in Excel (5 Fixes)
• [Fixed]: Can’t Find Project or Library Error in Excel (3 Solutions)