To get the path for an Excel file, you need to use the CELL function along with three more functions (LEN, SEARCH, and SUBSTITUTE). CELL helps you to get the complete path of the file including the file name and the worksheet name. And you need to use the rest of the functions to only get the file’s path out of it.
In this tutorial, we will look at getting the file path in three different ways.
Important: Make sure to save your Excel file before you use any of these formulas. This is not possible to get the path of the file unless you save it somewhere.
Get Path with the File and Sheet Name
You can use the below steps:
- First, save your workbook and give it a name.
- After that, enter the CELL function in the cell.
- From here, select the “filename” info_type, and let the reference blank.
- In the end, close the function and hit enter to get the result.
The moment you hit enter, it returns the file path along with the workbook name and sheet name in the result.
Get Path only with Workbook Name
In this situation, you need to remove the sheet name from the path. And for this, you need to use LEFT, SEARCH, and SUBSTITUTE.
Now to understand this formula, you need to split it:
- In the first part, enter the CELL function in a cell and use the filename in the info_type argument.
- Now, use the SEARCH function to get the position of the “]” closing square bracket from the full path.
- After that, using the position returned by the SEARCH, you need to use the LEFT to get the rest of the part from the path excluding the part after the ending square bracket.
- Next, we need to use the SUBSTITUTE to replace the starting bracket.
- In the end, instead of referring to cell A1, replace it with the actual CELL function.
Get File Path Only
Now let’s say you only need the path up to the folder name. For this, you need to use the CELL with SUBSTITUTE, LEN, RIGHT, and SEARCH functions.
To understand this formula, you need to split it:
- First, enter the CELL function in a cell and use the filename info_type.
- Now, use the SEARCH function to get the position of the starting square bracket and then add 1 to it.
- After that, use the LEN function to get the total number of characters in the path. And, subtract it from the position number returned by SEARCH.
- Next, use the RIGHT function to get the file name and sheet name from the full path. In the RIGHT, refer to the full path and use the number returned by the subtraction of the LEN and SEARCH for [num_char].
- From here, use the SUBSTITUTE to replace the file and worksheet name with a blank value from the original path.
- In the end, replace A1’s cell reference from the formula with the CELL’s function which you have in cell A1 to get it in a single formula.