Exceljet

Quick, clean, and to the point

Get workbook name only

Excel formula: Get workbook name only
Generic formula 
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
Summary 

To get the workbook name only (i.e. the file name without path or sheet name) you use a formula based on the MID function together with the FIND function. In the example shown, the formula in E5 is:

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

The result is the name of the workbook only, "example workbook.xlsx" in the example shown.

Note: the CELL function is called four times in the formula. 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.

Explanation 

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.