Apr 01 2022 02:49 PM
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
Apr 01 2022 06:33 PM
Apr 01 2022 08:07 PM
@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
Apr 01 2022 09:31 PM - edited Apr 01 2022 10:46 PM
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.
Apr 01 2022 11:26 PM
Apr 02 2022 04:44 AM
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.
Apr 01 2022 09:31 PM - edited Apr 01 2022 10:46 PM
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.