Get workbook name only

It may happen that sometimes you only want to extract the workbook name which is the file name without the sheet name or the path. You can perform this task in Excel using a formula which uses the MID and SEARCH function. This would give the result in .xlsx extension which would look like:

Workbook.xlsx

Generic formula

To get the workbook, we use the custom formula as shown:

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

Explanation

To extract only the workbook name, we combine the MID and the FIND SEARCH functions in Excel.

Firstly, the cell function is executed to get the file name and the path.

For example – CELL (“ filename” , A1 ) , this would result in:

Path [ workbook. xlsm ]  sheetname.

Next, the MID function is used to extract the file name from the path and sheet.

In order to calculate the starting position we use the FIND SEARCH function:

=SEARCH (“ [“ , CELL (“ filename” , A1) ]+1

The number of characters to be extracted is also done with the FIND SEARCH function as:

=SEARCH (“ [“ , CELL ( “ filename “ , A1 )) – 1

Example

As shown in the figure, we apply the formula in B1 as shown

Figure 1. Apply the formula in B1

Finally, the workbook name is displayed after execution of the entire formula as shown in the figure:

Figure 2. The file name “basic sort’ gets displayed in B1

In this case, the file name ‘basic sort’ gets displayed in B1.

So, this was the tutorial to get only the workbook without the sheet name in excel. Hope this would be helpful for you in the future.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar