Get workbook path only

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.