Excel formula: Get workbook path only

Generic formula

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

Explanation

If you want to get the workbook path (directory) only, you can do so with a formula that uses the LEFT and the FIND function. The result will look something like this:

Drive:\path\to\file\   //Windows
Drive:path:to:file:    //Mac

How the formula works

The cell function is used to get the full file name and path:

CELL("filename",A1)

The result looks like this:

path[workbook.xlsm]sheet

The full file name and path are fed into the LEFT function, which is used to extract just the full directory path.

The number of characters to extract by locating the the position of the left square bracket "[" with FIND and subtracting 1:

FIND("[",CELL("Filename",A1))-1

Author

Dave Bruns

Related formulas

Get full workbook name and path

If you want to get the current workbook's full path, name, and sheet with a formula, you can use the CELL function and a reference to any cell in the workbook. CELL will return the name in this format: path [ workbook.xlsx ] sheetname Note that you...

Get workbook name and path without sheet

If you want to get the current workbook's full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this: path [ workbook...

Get workbook name only

If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function. How the formula works The cell function is used to get the...

Get sheet name only

To get the name of the current worksheet (i.e. current tab) you can use a formula based on the CELL function . CELL retrieves the workbook name and sheet, and the MID and FIND functions are used to extract just the sheet name. In the example shown,...

Related functions

Excel CELL Function

The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the...

Excel LEFT Function

The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".

Excel FIND Function

The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.