Exceljet

Quick, clean, and to the point

Get workbook name and path without sheet

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

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

 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.