Excel Formula to Display the Sheet Name in Cell

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"),"]")