Go Back

Get workbook name only

Excel allows us to obtain the workbook name of the file we are working on, by using the CELL, MID and SEARCH functions.  The CELL function provides information about a cell such as the cell’s address, format or contents.  The MID function extracts a substring from the middle of a string while the SEARCH function returns the starting position of a substring within a string. This step by step tutorial will assist Excel users in getting only the workbook name of an Excel file.  

Figure 1. Final result : Get the workbook name of the Excel file “Test”

Final formula: =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH(".",CELL("filename"))-1-SEARCH("[",CELL("filename")))

Syntax of the CELL function

=CELL(info_type, [reference])

where

  • info_type – a text which specifies the type of information about the cell that we want to obtain; allowed values are: address, col (for column), color, contents, filename, format, parentheses, prefix, protect, row, type, width
  • reference optional; the cell that we want to obtain information about; if omitted, the reference cell is the cell that was last modified

Syntax of the MID function

=MID(text, start_num, num_chars)

where

  • text – the text string containing the substring we want to obtain
  • start_num – the position of the first character of the substring we want to obtain
  • num_chars – the number of characters we want MID to return

Syntax of the SEARCH function

=SEARCH(find_text, within_text, [start_num])

  • find_text – the substring that we want to find
  • within_text -the text string where we will search for the value of the find_text argument
  • start_num optional; the position of the character in the within_text argument where we want to start searching

Get workbook name only

We want to get only the name of the workbook.

This is the working formula using the CELL, MID and SEARCH functions:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH(".",CELL("filename"))-1-SEARCH("[",CELL("filename")))

Figure 2. Entering the formula to get the workbook name

To apply the formula, we follow these steps:

  • Select cell C2
  • Insert the formula: =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH(".",CELL("filename"))-1-SEARCH("[",CELL("filename")))
  • Press enter

Figure 3. Result for the workbook name: “Test”

The formula returns the workbook name: “Test”.  

Explanation

Working formula:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH(".",CELL("filename"))-1-SEARCH("[",CELL("filename")))

To understand the formula, we divide the MID formula into three segments.  

First segment : CELL("filename")

Second segment: SEARCH("[",CELL("filename"))+1

Third segment: SEARCH(".",CELL("filename"))-1-SEARCH("[",CELL("filename"))

Figure 4. Arguments for the MID formula to get the workbook name

Remember that the syntax for the MID function is:

=MID(text, start_num, num_chars)

  • The first segment serves as the text – which returns the cell filename C:\Users\00babierce\Desktop\[Test.xlsx]Sheet2
  • The second segment is the argument for start_num – the position of the first character of the workbook name “Test” ; in this case, start_num is 30 or the 30th character of the cell filename
  • The third segment is the argument for num_chars – the number of characters we want MID to return, which is 4 (Test has 4 characters)

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have a workbook with several sheets saved on harddrive but when I attempt to open it all I get is a highlighted new excel workbook showing no cells and no file name
Solved by K. U. in 20 mins
Hi, I have to collect data from different workbook and consolidate in new workbook. Each workbook has the common identifier to identify the rows also each sheet i need only specific fields. How to select specific fields from each workbook and place it in new worksheet. Pls guide. Eg: First sheet has id and name, second sheet has id, name , owner , description third sheet has name,business owner, severity, Fourth sheet Contact number.name I want to collect all the information and have it in the new workbook
Solved by O. J. in 26 mins
I would like to create a function that will get only the last name on a certain group of name with a complete LN, FN & MN.
Solved by G. H. in 13 mins

Leave a Comment

avatar