In this article, we discuss different tasks related to Excel sheet names. We can get the sheet names using Excel functions and VBA, user-defined functions, etc. This article will also cover how to rename or reference sheets in Excel.
This is useful if you want to include the sheet name in your printed output. For example, you could have a title that says “Sales Report – Q1 2023”. You could use the sheet name to reference data on a different sheet. Excel sheet name is also important while organizing data, collaborating with others, data analysis, etc.
Download Practice Workbook
You can download the practice file here.
Get Excel Sheet Name Using Excel Formula
1. Using TEXTAFTER Function with CELL Function
- In the beginning, insert the below-mentioned formula in cell C5.
=TEXTAFTER(CELL("filename",A1),"]")
Formula Explanation
- (CELL(“filename”,A1): The CELL function returns the full path, filename, and sheet name of the current workbook.
- TEXTAFTER(CELL(“filename”,A1),”]”): This formula will return the text after the closing bracket in the cell value.
- It will show the sheet name in cell C5.
- You can insert the following formula to show the formula in cell B5.
=FORMULATEXT(C5)
2. Using the MID & FIND Functions with CELL Function
- Here, insert the given formula in cell C5.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Formula Explanation
- MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255): This formula extracts the text right after the closing square bracket (]+1).
- You will find the sheet name in cell C5.
3. Using RIGHT, LEN & FIND Functions
- First, insert the following formula in cell C5.
=RIGHT(CELL("filename"), LEN(CELL("filename")) - FIND("]", CELL("filename")))
Formula Explanation
- FIND(“]”, CELL(“filename”)): This part of the formula finds the position of the closing square bracket (]) within the full filename path.
- LEN(CELL(“filename”)) – FIND(“]”, CELL(“filename”)): It calculates the number of characters from the closing square bracket to the end of the filename.
- RIGHT(CELL(“filename”), LEN(CELL(“filename”)) – FIND(“]”, CELL(“filename”))): This formula extracts the substring from the right, starting from the position right after the closing square bracket (]+1) up to the end of the filename.
4. Using INDEX, MID and FIND Functions
- First, insert the following formula in cell C5.
=INDEX(MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,255),1)
Formula Explanation
- MID(CELL(“filename”,B1),FIND(“]”,CELL(“filename”,B1))+1,255): The formula extracts the substring starting from the position right after the closing square bracket (]+1).
- INDEX(MID(CELL(“filename”,B1),FIND(“]”,CELL(“filename”,B1))+1,255),1): The INDEX function returns the first (1) character of the extracted substring, which represents the sheet name.
Get Sheet Name VBA
1. Applying User-Defined Function
- First, we will open the Developer tab and select the Visual Basic command.
- After that, from the Insert tab, we will choose the new Module to write the VBA code.
- Now, insert the below-mentioned UDF (User Defined Function) in the module.
Function SheetName()
SheetName = ActiveSheet.Name
End Function- After that, insert the function in any cell of the sheet, and you get the sheet name in that sheet.
2. Using VBA Code
- First, insert the following code in the module.
Sub SheetName2()
Range("C5") = ActiveSheet.Name
End Sub- Then, run the module, and you will see the sheet name in cell C5.
Rename a Worksheet in Excel
- First, take the cursor over the sheet name and right-click on the mouse.
- Then, select the Rename option from the Context Menu.
- Or you can just double-click on the sheet name and will see the edit cursor on it.
- You can rename using any of the ways mentioned above.
Excel Reference Sheet Name
- First, write the sheet name with an exclamation mark (!) and then put the cell address like the following formula.
=VBA!C5
- In the same way, you can reference other sheets too.
Using the CELL Function for File Path
- First, insert the following formula in cell C5.
==CELL("filename")
- It will show the full path location of the Excel sheet in cell C5.
Things to Remember
- An Excel workbook must contain a unique name for each sheet. Excel doesn’t allow two sheets with the same name in a workbook.
- Excel sheet names are case-insensitive. So you can name using upper or lower case.
Frequently Asked Questions
1. What characters are not allowed in Excel sheet names?
You can not use certain characters in Excel sheet names. Such as backslash (), forward slash (/), asterisk (*), question mark (?), opening square bracket ([), closing square bracket (]), colon (:), and some other characters.
2. Can I use spaces in Excel sheet names?
Yes, you can use spaces in Excel sheet names. However, it is good practice to avoid spaces and underscores (_) or camel case (e.g., SheetName), as it can make referencing sheets easier in formulas.
3. Can I use numbers in an Excel sheet name?
Yes, you can use numbers in an Excel sheet name. Excel allows you to include numerical characters (0-9) in the sheet name. However, the sheet name cannot start with a number or consist solely of numbers.
Conclusion
In this article, we showed how to get Excel sheet names using different functions and also using VBA. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Excel Sheet Name: Knowledge Hub
<< Go Back to Excel Worksheets | Learn Excel
