Get workbook name only

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

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

To extract the name, the formula works in 3 steps:

1. Get the full path and filename
2. Locate the opening square bracket ("[")
3. Locate the closing square bracket ("]")
4. 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:

"C:\examples\[example workbook.xlsx]Sheet1"

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:

=MID(CELL("filename",A1),14,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

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:

=LET(path,CELL("filename",A1),MID(path,FIND("[",path)+1,FIND("]", path)-FIND("[",path)-1))

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:

=LET(path,CELL("filename",A1),osb,FIND("[",path),MID(path,osb+1,FIND("]",path)-osb-1))

In this version, the location of the opening square bracket "[" (osb) is calculated just once.