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?

Answer 1

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)