Exceljet

Quick, clean, and to the point

Get workbook path only

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

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
Explanation 

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 
Excel formula: Get full workbook name and path
Get full workbook name and path
The CELL function can return various information about a worksheet. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. The type of information to be returned is specified by the info_type...
Get workbook name and path without sheet
The CELL function is used with "filename" to get the full workbook name and path: CELL ( "filename" , A1 ) The result is a full path that looks like this: path [ workbook.xlsm ] sheetname The LEFT function receives the full path as text,...
Excel formula: Get workbook name only
Get workbook name only
The cell function is used to get the full file name and path: CELL ( "filename" , A1 ) The result looks like this: path [ workbook.xlsm ] sheetname At the highest level, the MID function simply extracts the file name from the path and...
Excel formula: Get sheet name only
Get sheet name only
The CELL function is used to get the full file name and path: CELL ( "filename" , A1 ) The result looks like this: path [ workbook.xlsm ] sheetname CELL returns this result to the MID function as the text argument. The sheet name begins...
Related functions 
Excel CELL function
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
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
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.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.