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.
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,31)

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

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.
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

Joining RIGHT, LEN, CELL, and FIND Functions to Get Worksheet Name in Excel

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


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.
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Find All Sheet Name in Using Excel INDEX Function

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.
=INDEX(Sheet,F5)

Find All Sheet Name in Using Excel INDEX Function

Step 4:

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

Find All Sheet Name in Using Excel INDEX Function

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.

Excel Sheet Name in Cell Using Custom VBA Function

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

Excel Sheet Name in Cell Using Custom VBA Function

Step 4:

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

Excel Sheet Name in Cell Using Custom VBA Function

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

Find Excel Sheet Name Applying VBA Code

Step 2:

  • Hit F5 to run the code.

Find Excel Sheet Name Applying VBA 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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo