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. 

Generic formula

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

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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.