SOLVED

File Name

New Contributor

Hi There,

What kind of formula should I use to have the excel file name in a cell WITHOUT the extension of the file,

Thanks,

Ali

7 Replies
I think you need to provide a little more information here. For example, where is file name coming from? Do you have a whole column of file names, including their extensions, and just want to strip off the extensions? And so forth. The answer to your question is probably fairly easily--Excel can readily do text manipulation--but it would help if you could provide a bit more info on the circumstances in which you want to remove the extension(s).

@Alireza2291 If you need only file name in a cell WITHOUT the extension & path, below is the formula

 

=RIGHT(LEFT(CELL("filename"),(SEARCH(".",(CELL("filename"))))-1),LEN(LEFT(CELL("filename"),(SEARCH(".",(CELL("filename"))))-1))-(SEARCH("[",(LEFT(CELL("filename"),(SEARCH(".",(CELL("filename"))))-1)))))

 

paste the above formula in any cell

save the workbook with any name and close it

open it and you will see the file name without extension

attached are sample files, save them on your computer & open it you will see the result

best response confirmed by Grahmfs13 (Microsoft)
Solution

@Alireza2291  Perhaps this:

 

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

 

It assumes that the extension you seek is ".xlsx" since you are working in an Excel file. If you are on a recent version you can avoid the receptive references to CELL() by using the LET function. 

Sir, your formula is good as it is short.
In my formula you need not to bother about the length of extension.
My formula will handle both .xls as well as .xlsx

@Vimal_Gaur 

On the other hand your formula doesn't work if the file is synced with OneDrive/SharePoint folder.

@Riny_van_Eekelen formula works with both extensions if change *.xlsx on *.xls. And both for synced and not synced folders.

Great Sir
Thank you so much sir,