=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-1) returns the workbook name — Excel stores it in square brackets inside the full path, so this pulls out what sits between them. In Excel 365, =TEXTAFTER(TEXTBEFORE(CELL("filename",A1),"]"),"[") does the same thing far more readably.
The result includes the extension; strip it with =TEXTBEFORE(...,"."). Two requirements: the file must have been saved at least once, or CELL returns nothing, and the reference argument (,A1) should always be included.
In Excel, there is no direct function to get the name of the file, but you can use the CELL function to get the path of the file, and then we can use the MID function to extract the file name from it.
You can use the following steps:
- First, you need to enter the CELL function in a cell.
- After that, you need to specify the “filename” in the argument.
- Next, enter the closing parentheses
- In the end, hit enter.
=CELL("filename")At this point, you have the address path of the file. You can see in the following example that I have the path address in cell D1.
Now the next thing is to get the name of the file from this address, and for this, we will use the MID + SEARCH functions.
There’s one thing that you need to note down the name of the file is in the square brackets. So, let’s create a formula to get the name of the file.
- First, use the SEARCH function to know the position of the starting square bracket by using the search function.
- Next, you need to find the position of the ending square brackets.
- After that, you need to use the MID function to get the fine name from the entire path.
- And for this, we need to use the following formula where you need to use the values that you have got from the search function.
Now let’s understand this formula that we have written with the MID function. In the MID function:
- In the first argument, you have referred to the cell where we have the full path of the file.
- In the second argument, you have used the position of starting square bracket which you have got from the SEARCH function and then added 1 into it.
- In the third argument, you used the position of the ending square bracket and deducted the position of the starting square bracket, and after that deducted 1 from it. This gives you the exact number of characters to get.
Here I have input all the functions in a single formula to get the result in a single cell.
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.