Get workbook name and path without sheet in Excel

If you want to get the current workbook’s full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this:

path[workbook.xlsm]

Formula

=SUBSTITUTE( LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

Explanation

How the formula works

The CELL function is used with “filename” to get the full workbook name and path:

CELL("filename",A1)

The result is a full path that looks like this:

path[workbook.xlsm]sheetname

The LEFT function receives the full path as text, along with the the location of square bracket “]” (minus 1) which is calculated by the find function:

FIND("]",CELL("filename",A1))

With these arguments, LEFT effectively strips off the sheet name out of the path. However, the left square bracket “]” remains:

path[workbook.xlsm

The final function, SUBSTITUTE, removes the “]” by replacing it with an empty string (“”).

=SUBSTITUTE(path[workbook.xlsm,"[","")