There are times when we need to get the name of the Excel file, the path or the worksheet that we are working on. The following formula shows you how to do so.

Firstly, to get the full path, file name and current worksheet name in a cell, the formula is
=CELL(“Filename”)

The rest of the formulas needed are shown below. For each of the formula, you can replace “B$2” with the formula in B$2 accordingly if you need the formula in a single cell.

The actual results of the formula in Column B are shown below.

However, in the unlikely event that any of your folders in the path or the file name contains square brackets “[” or “]”, you would need a set of different formulas. (It’s a pain, so try not to have any of your folders or file names to have square brackets!)

The actual results in Column B are shown below (it’s the same as the one above).

This latter set of formulas makes use of the formula for finding the last occurrence of a character in a string.

In summary, the formulas to find the following (assuming no square brackets in your folder path or file name) are:

(1) Folder path
=LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)

(2) File name with path
=SUBSTITUTE(LEFT(CELL(“Filename”),FIND(“]”,CELL(“Filename”))-1),”[“,””)

(3) File name without path
=MID(CELL(“Filename”),FIND(“[“,CELL(“Filename”))+1,FIND(“]”,CELL(“Filename”))-FIND(“[“,CELL(“Filename”))-1)

(4) Sheet name
=RIGHT(CELL(“Filename”),LEN(CELL(“Filename”))-FIND(“]”,CELL(“Filename”)))

Let me know if you need any explanation of the above. Thanks!