Get workbook name and path without sheet

An excel formula to get workbook name and path without sheet

Related Functions

Sponsored Link

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

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]

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

 

Sponsored Link

0 votes. 0 / 5