Exceljet

Quick, clean, and to the point

Get workbook path only

Excel formula: Get workbook path only
Generic formula 
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
Summary 

To get the path to the current workbook without workbook name, you can use a formula based on the CELL function, together with the LEFT function and FIND function. In the example shown, the formula in E5 is:

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

The result is a path without the filename like this: "C:\path\".

Explanation 

In this example, the goal is to get the workbook path without workbook name. The formula used to perform this task appears in cell E5:

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

At a high level, this formula works in 3 steps:

  1. Get the full path and filename
  2. Locate the opening square bracket ("[")
  3. Extract all text up to the opening square bracket ("[")

Get path and filename

To get the path and file name, we use the CELL function like this:

CELL("filename",A1) // get path and filename

The info_type argument is "filename" and reference is A1. The cell reference is arbitrary and can be any cell in the worksheet. The result is a full path like this as text:

C:\examples\[workbook.xlsx]Sheet1

Note the sheet name (Sheet1) appears at the end, and workbook name appears inclosed in square brackets, [workbook.xlsx].

Locate the opening square bracket

The location of the opening square bracket ("[") is calculated with FIND like this

FIND("]",CELL("filename",A1))-1 // returns 12

The FIND function returns the location of "[" (13) from which 1 is subtracted to get 12. We subtract 1 because we want to remove all text starting with the "[" that precedes the workbook name. Or, to put it the other way, we want to extract all text up to the "[".

Extract path

In the previous step, we located the "]" at character 27, then stepped back to 12. This number is returned directly to the LEFT function as the num_chars argument. The text argument is again provided by the CELL function as described above:

=LEFT(CELL("filename",A1),12)
=LEFT("C:\examples\[workbook.xlsx]Sheet1",12)

The LEFT function returns the first 12 characters of text as the final result:

C:\examples\

LET function improvement

The CELL function is called twice in the formula because we need the path twice, once for the FIND function to locate the opening square bracket ("["), and once for the LEFT function to extract all text before the "[". 

In Excel 365, the LET function makes it possible to declare and assign variables inside a formula. With LET, the formula above can be streamlined somewhat by declaring and defining a "path" just one time like this:

=LET(path,CELL("filename",A1),LEFT(path,FIND("[",path)-1))

The formula logic is the same as explained above, but the CELL function is used just once.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.