Get workbook name and path without sheet
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:
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.
In this example, the goal is to get the workbook name and path without the sheet name included. The formula in E5 is:
At a high level, this formula works in 4 steps:
- Get the full path and filename
- Locate the closing square bracket ("]")
- Remove sheet name and "]"
- 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:
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
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:
The LEFT function returns the first 26 characters of text.
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 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:
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:
The formula logic is the same as explained above, but the CELL function is used just once.