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:
To get the workbook, we use the custom formula as shown:
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
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.