The result is a path without the filename like this: “C:\path". At a high level, this formula works in 3 steps:
Get path and filename
To get the path and file name, we use the CELL function like this: 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: 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 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: The LEFT function returns the first 12 characters of text as the final result:
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: The formula logic is the same as explained above, but the CELL function is used just once.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
