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.
Leave a Comment