In this example, the goal is to get the workbook name only without path or sheet name included. The formula in E5 is:
To extract the name, the formula works in 3 steps:
- Get the full path and filename
- Locate the opening square bracket ("[")
- Locate the closing square bracket ("]")
- Extract the text between square brackets
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:
The path starts with the drive and includes both the workbook name and the Sheet name. Notice the workbook name appears enclosed in square brackets.
Locate opening square bracket
The location of the opening square bracket ("]") is determined like this
FIND("[",CELL("filename",A1)) // returns 13
The FIND function returns the location of "[" (13) . We then add 1 because we want to extract text starting one character after the "[". We can now rewrite the formula like this:
Locate closing square bracket
Next, we locate the closing square bracket "]" in the same way with the FIND function:
FIND("]", CELL("filename",A1)) // returns 35
The FIND function returns 35. This means we want to extract text up through character 34.
Extract text between square brackets
The final step is to extract the text between the square brackets, and this is done with the MID function. We know where we want to start (14) and where to end (34). However, while the MID function takes start_num as the starting position, there is no equivalent end_num. Instead, MID takes num_chars, the number of characters to extract. Therefore, we need to calculate num_chars by subtracting the opening bracket "[" location from closing bracket "]" location:
=35-FIND("[",CELL("filename",A1))-1 =35-13-1 =21
After subtracting 1, the result is 21. We subtract 1 because we don't want to include the closing "]". We now have:
=MID(CELL("filename",A1),14,21) =MID("C:\examples\[example workbook.xlsx]Sheet1",14,21) ="example workbook.xlsx"
LET function improvement
The CELL function is called four times in the formula. This happens because the path is not a value that exists in the worksheet, so each time path is needed, it must be fetched again with the CELL function.
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 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 to set the variable path. You might notice that the FIND function is still used two times to find the opening square bracket. Following the same approach above, we could add another variable (osb = "open square bracket") to simplify further still like this:
In this version, the location of the opening square bracket "[" (osb) is calculated just once.