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:
- Get the full path and filename
- Locate the opening square bracket ("[")
- 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.