This blog post looks at using an Excel formula to display the sheet name in cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet tab name.

This tutorial covers two Excel formula examples. Firstly, using the CELL, MID and FIND functions. And then using the TEXTAFTER function with CELL.

Let’s begin by looking at the CELL function.

## CELL Function in Excel

The CELL function is a fantastic, and relatively unknown, function in Excel. Its purpose is to return information about a cell such as its column, cell colour, its protected state, or filename.

It requires the information to return, entered as a string, and the reference to return the information from. A list is provided of the different types of information CELL can return.

We need to use the function to return the filename from a given cell. Because our goal is to return the worksheet name, it does not matter what cell we use. Any cell on the sheet will work. In this example, cell A1 has been used (choosing any other cell would be weird, no?).

`=CELL("filename",A1)`

This function above will return the full filename of the cell such as;

`C:\Users\Computergaga\Desktop\[return-sheet-name.xlsx]Chicago`

## Returning Just the Sheet Name to a Cell

Now that we have the filename, we need to extract just the sheet name. The MID and FIND functions will be used for this.

The FIND function is used to return the position of the closing “]” + 1. The “]” indicates the end of the workbook reference, so the +1 returns the index for the first letter of the sheet name.

The MID function then extracts up to the next 32 characters. This is an excessive number but because sheet names cannot have more than 31 characters it ensures the full name is returned whatever it may be.

Here is the full Excel formula to display the sheet name in a cell;

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

## Watch the Video

The CELL function is used twice in this Excel formula. This is fine, but to reduce calculation, the LET function could have been used to store the CELL formula result to be referenced later. This means it only calculates once, rather than twice.

`=let(`

filepath,CELL("filename",B2),

MID(filepath,FIND("]",filepath)+1,32)

)

## Using the TEXTAFTER Function

In Excel 365, there are two new functions named TEXTBEFORE and TEXTAFTER. The TEXTAFTER function makes the process of returning a sheet name to cell even easier.

As its name suggests, the TEXTAFTER function returns all text after a given delimiter. It also has other cool features beyond the scope of this requirement.

In this formula the TEXTAFTER function uses the string returned by the CELL function and returns all text after the closing square bracket “]” delimiter. This time, the reference is omitted from the CELL function. When a reference is omitted, the cell that the formula belongs to is used.

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

Neil Watkins says

Great function. I can get staff members to now remember where they put their files by this function. Thanks

Tom says

Is there any way to reverse engineer this, so the sheet name is determined by a cell value?

computergaga says

This can only be done with a macro using a statement such as Activesheet.Name = Range(“A1”).Value

Brian S Blackwell says

Sorry, but the filename (workbook name) is not the same as the sheet name inside a workbook.

“A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.”

Alan Murray says

I know, Brian. The formula returns the filename and extracts the sheet name from it.

Love Rai says

Note :- Formula Work only if your Sheet Name in Numeric

=”=’E:\Contacts\”&”[“&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”‘!$B$1048576″

Use this Formula To Get Value from other Excel Sheet

________________

Define Path = “=’E:\Contacts\”&”[”

Give Sheet Name [if Sheet Name in Numericl] = SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1

Cell No. = !$B$1048576

Johan Jordaan says

Is there a difference between Excel in Windows and Excel on Macbook?

Alan Murray says

Yes, there are differences between the two. Not in formulas but in other areas especially VBA and Power Query.

Excel101 says

Hi! I’m just wondering what’s the meaning of the numbers +1 and 32?

Alan Murray says

The + 1 is to start from the first character after the “]” and the 32 is a number to just ensure we return enough characters. 32 is more character than we require. We do not know how many characters in the sheet name, but we know 32 is more than enough.