Remove file extension from file name returned by formula

[ozbeachbum]

Hi,
I have the following formula which returns the workbook's file name: 20 CHRONICLE trades.xlsx
LET(f,CELL("filename",A1),REPLACE(REPLACE(f,FIND("]",f),500,""),1,FIND("[",f),""))
I would like it to return the file name minus the file extension: 20 CHRONICLE trades
Cheers.

 

[Eric W]

If they're all .xlsx files, then

=LET(f,CELL("filename",A1),r,REPLACE(REPLACE(f,FIND("]",f),500,""),1,FIND("[",f),""),LEFT(r,LEN(r)-5))

 

[StephenCrump]

Or you could try:

=LET(f,CELL("filename"),m,FIND("[",f),n,FIND(".",f,m),MID(f,m+1,n-m-1))

(assuming you don't have any "." characters in the filename)

 

[ozbeachbum]

Thanks Eric,
The majority are .xlsx files, however I do have some Word files also.
Would you be good enough to let me know what would be need for them.
Cheers,
Dave.

 

[ozbeachbum]

Or you could try:

=LET(f,CELL("filename"),m,FIND("[",f),n,FIND(".",f,m),MID(f,m+1,n-m-1))

(assuming you don't have any "." characters in the filename)

Thanks Stephen.

 

[Eric W]

Thanks Eric,
The majority are .xlsx files, however I do have some Word files also.
Would you be good enough to let me know what would be need for them.
Cheers,
Dave.

I'm not sure how you can use an Excel formula in a Word file? In any event, all my formula does is remove the last 5 characters of the file name, so .xlsx. But Word files end in .docx so that works too. I was more worried about older files that only have .xls on them.

 

[ozbeachbum]

OK,
Thanks.