How to Get a Sheet Name Using a Formula in Excel - 3 Methods

When working in a workbook with a lot of different tabs, being able to pull, reference, and use specific sheet names can save you a lot of time. In this tutorial we'll go over a couple different methods of how to get sheet names using an formula.

Contents:

Possible Use Cases

Method 1 - Sheet Name Using RIGHT and FIND Functions (Older Method)

Method 2 - Sheet Name Using TEXTAFTER (Easiest Method)

Method 3 - Sheet Name Using TEXTSPLIT (Alternative Method)

Possible Use Cases

There are a few main use cases that you may find yourself needing to pull the names of specific sheets that you're working on.

The first and most basic use case would be for titling purposes. Often, you'll want a sheet named "Q1 2022 Forecast" or "Sales Dashboard", on the sheet itself to let others know what content is contained in the sheet. Using this formula can speed up your workflow and reduce time needed to make many various copies and versions of a worksheet.

Similarly, let’s say you have a workbook with 30 sales employees comp data and the template sheet runs lookups and different SUMIFS based on employee ID. Instead of creating 30 copies and having to rename every cell on the page as well as the sheet name, using this method you could just leave the sheet name as the employee ID and reference the sheet name, leaving you with only the sheet name to update for each new copy.

You may also wish to reference sheet names within formulas. Using the below methods as a base to pull in the correct sheet name will avoid hardcoding in sheet names and reducing the possibility of errors.

Methods

1. Sheet Name Using RIGHT and FIND Functions (Older Method)

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

There are a few different parts of this formula to break down. The most important is "CELL("filename")". This function will always return the full file pathway in which the sheet is found. From your hard drive, to the folders, the workbook name in square brackets, and then finally the sheet name.

In order to extract only the sheet name, we need to use the RIGHT formula to pull that out of this long string. However, because the sheet names can vary in length, we need a way of figuring out how many characters make up the sheet name.

To do this, we use the FIND function, which tells us where the "]" character occurs in the full pathway. Since we want everything to the right of that character, we can then take the length of the entire filename using LEN, and subtract out how many characters to the left of the "]" character exist, leaving us with the number of characters in the sheet name.

From there we simply use the RIGHT function to extract that specific amount of characters from the full file path.

Combined all together, we see that of the total length 76 characters, 59 of those characters come before the "]" character, and after subtracting those two numbers, you're left with 17 characters, the length of the file name.

This can be cleaned up using the LET function if you are running a new version of Excel. If not, you may have to use the function above.

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

Using LET allows us to assign "CELL("filename")" to a variable filename, and reference that in the formula rather than repetitively calling "CELL("filename")".

This visually cleans up the formula and makes it a little bit easier to track what is going on.

2. Sheet Name Using TEXTAFTER Function (Easiest Method)

= TEXTAFTER(CELL("filename"),"]")

The TEXTAFTER function is fairly new to excel but allows us the easiest method of pulling the sheet name. The TEXTAFTER function will return any text from a cell after a specified delimiter.

Since every time we reference =CELL("filename") the workbook name will be surrounded in square brackets, followed by the sheet name, we want all of the text after the "]" character.

This formula is simply taking all of the text that comes after the "]" character in the filename, leaving us with the sheet name.

3. Sheet Name using TEXTSPLIT Function (Alternative Method)

= INDEX(TEXTSPLIT(CELL("filename"),"]"),,2)

Because TEXSPLIT actually splits an input into separate columns, if we just use TEXSPLIT without the INDEX, you get two dynamically spilled cells in an array. The first cell containing everything before the "]" delimiter, and the second cell containing everything after the "]" delimiter.

By adding the INDEX to this function, we can tell Excel that, of the two cells, we wish to only return the second cell in the array. Leaving us with the sheet name only.