Insert Worksheet Name Into a Cell - Excel Tips

How to insert the worksheet name into a cell  ?

This post will show you how to insert the worksheet name into a cell without using any VBA code. There isn't a single Excel function to do it but there are several possible combinations of Excel functions that will return the worksheet name into a cell where you add the formula.

Today we will use excel functions: MID, CELL and FIND.

MID(text, start_num, num_chars)

CELL(info_type, [reference])

FIND(find_text, within_text, [start_num])

The combination of these Excel Functions that will return the worksheet name into a cell is:

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

Basically the CELL("filename",A1) returns the full file name including the worksheet name. Afterwards the FIND function will search for the "]" on the full file path (which always appear before the worksheet name) and returns the starting position from where the MID function should retrieve the worksheet name.

IMPORTANT NOTES:

• The formula will only return a valid result in case the workbook you're working on is already saved. You need to save the workbook so it actually has a name that can be returned by the CELL function successfully
• Using the cell reference (A1) in each instance of the CELL function will force this function to return the name of the worksheet that contains that reference. In case you don't use the reference, the formula will return the name of the active worksheet. (E.g. if you add this formula without using reference on a cell C10 of Sheet1 and then go to Sheet2 and refresh it (F9), the cell C10 of Sheet1 will contain "Sheet2", which isn't the expected result)