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
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:
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.