Exceljet

Quick, clean, and to the point

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

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.