triming extension from filename in Excel

Question

I have this formula. This works great but fails when there are 5 characters including the dot.

For example,

abcdefgh.pdf =TRIM(LEFT(A1,LEN(A1)-4)) gives me abcdefgh xyz.xlsx =TRIM(LEFT(A2,LEN(A2)-4)) gives me xyz.

Is there a formula that looks for the last dot and trims everything after the last dot?

yes, you can use `Find`: http://www.techrepublic.com/article/using-excels-find-and-mid-to-extract-a-substring-when-you-dont-know-the-start-point/1033368 — paul, Apr 17 '13 at 15:00
Just a precaution but using the `TRIM` function in here, unless it is your intent to remove any leading/trailing spaces in the filename after you've removed the extension, which might not be desired. — David Zemens, Apr 17 '13 at 15:04

score 15 · Accepted Answer · answered Apr 17 '13 at 15:05

15

Try this formula to get rid of the last dot and everything after

=LEFT(A1,LOOKUP(2^15,FIND(".",A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)

If there's only ever one dot as per your examples then you only need

=LEFT(A1,FIND(".",A1)-1)

answered Apr 17 '13 at 15:05

barry houdini

45,615
8
63
81

Thanks so much. Worked in first try. – torres Apr 17 '13 at 15:08

score 0 · Answer 2 · answered Jun 03 '15 at 14:28

This formula works well

=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",100)),100))

It replaces each period with 100 spaces and then returns the right 100 characters and trims it. You can also replace "." with "\" to get the filename.

Credit to NBVC at excelforum.com

score 0 · Answer 3 · answered May 30 '16 at 08:16

Could try below formula.

=SUBSTITUTE(A1,"."& RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,".","|",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))),"")

This works for file extensions with 2 to 7 character file extensions with full paths and namespaces notation in filename.

you could also use

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,".","|",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))

to find the extension of the file.

triming extension from filename in Excel

3 Answers3

Linked