Sometimes you'll find yourself needing to pull a file's pathway into your Excel sheet. Luckily there is a quick formula we can use to extract the file pathway of any workbook.
Contents:
1. Formula
Formula
= LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)Explanation
How to Extract File Path of an Excel File Using a Formula
While there is no built-in formula to return the file pathway for an Excel workbook, we can use the CELL function combined with a few other functions to return the full file path, name, and even sheet name if we want.
The base of our formula is CELL:
= CELL("filename",A1)
By using the CELL function with the "filename" info_type in the first argument position, this function will return the full file path, the workbook name in brackets, and the sheet that the formula is placed in.
This formula will return the full file path and sheet name of the file where the formula is used, based on the reference cell (in this case, A1).
One thing to remember, the CELL function will only update when the workbook is saved if you are using the "filename" info_type. If the workbook has not been saved, CELL will return an empty string.
If you don't want the sheet name or workbook name, and want to isolate the file path, you would need to use additional functions to separate them out.
Here is a formula that can help you extract the file path only:
= LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)
This formula finds the position of the "[" character (which precedes the workbook name in the full file path) and then returns all text to the left of this position, which is the file path.