Exceljet

Quick, clean, and to the point

Get workbook name and path without sheet

Excel formula: Get workbook name and path without sheet
Generic formula 
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")
Summary 

To get the name and path of the current workbook without a sheet name, you can use a formula based on the CELL function, together with the LEFT function, the FIND function, and the SUBSTITUTE function. In the example shown, the formula in E5 is:

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

The result is a path and filename like this: "C:\path\workbook.xlsx".

Note: the CELL function is called twice in the formula because we need the path twice, once for the FIND function to locate the "]", and once for the SUBSTITUTE function to remove the "]". In Excel 365, the LET function makes it possible to call CELL just once, as explained below. The CELL function will not return a value for filename until the workbook is saved. CELL is a volatile function and can cause performance problems in larger or more complicated worksheets. 

Explanation 

In this example, the goal is to get the workbook name and path without the sheet name included. The formula in E5 is:

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

At a high level, this formula works in 4 steps:

  1. Get the full path and filename
  2. Locate the closing square bracket ("]")
  3. Remove sheet name and "]"
  4. Remove the opening square bracket ("]")

Get path and filename

To get the path and file name, we use the CELL function like this:

CELL("filename",A1) // get path and filename

The info_type argument is "filename" and reference is A1. The cell reference is arbitrary and can be any cell in the worksheet. The result is a full path like this as text:

C:\examples\[workbook.xlsx]Sheet1

Note the sheet name("Sheet1") appears at the end.

Locate the closing square bracket

The location of the closing square bracket ("]") is calculated like this

FIND("]",CELL("filename",A1))-1 // returns 26

The FIND function returns the location of "]" (27) from which 1 is subtracted to get 26. We subtract 1 because we want to remove all text starting with the "]" that follows the filename. 

Remove sheet name

In the previous step, we located the "]" at character 27, then stepped back to 26. This number is returned directly to the LEFT function as the num_chars argument. The text argument is again provided by the CELL function:

LEFT("C:\examples\[workbook.xlsx]Sheet1",26)

The LEFT function returns the first 26 characters of text.

C:\examples\[workbook.xlsx

At this point, LEFT has effectively removed the sheet name, but notice the opening square bracket "[" remains.

Remove opening square bracket

The result from LEFT is returned to the SUBSTITUTE function as the text argument:

=SUBSTITUTE("C:\examples\[workbook.xlsx","[","")

SUBSTITUTE is configured to remove the opening square bracket by setting old_text to "[" and new_text to an empty string (""). The final result returned by SUBSTITUTE is:

C:\examples\workbook.xlsx

LET function improvement

In Excel 365, the LET function makes it possible to declare and assign variables inside a formula. With LET, the formula above can be streamlined somewhat by declaring and defining a "path" just one time like this:

=LET(path,CELL("filename",A1),SUBSTITUTE(LEFT(path,FIND("]",path)-1),"[",""))

The formula logic is the same as explained above, but the CELL function is used just once.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.