File Extension Remover

[binar]

Fellow Forum Members,
I have a list of files that require I remove all file extensions (including the period)

I would be very grateful if some one out there could help combine both formulas shown below into a single conditional IF statement formula:

=LEFT(F12,LEN(F12)-5)

=LEFT(F12,LEN(F12)-4)


The result I'm seeking handles removing both 4 and 5 character extensions. For example:

IF file extension = .mpeg or .divx , etc... use this formula "=LEFT(F12,LEN(F12)-5)"

OTHERWISE

IF file extension = .avi or .mov or .wmv or .mpg , etc... use this formula "=LEFT(F12,LEN(F12)-4)"


Any help will be greatly appreciated. Thank you very much.

 

[VoG]

Try

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

 

[xenou]

Another shot (in case a . is in the filename too):
=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",100)),100)))-1)

 

[binar]

Try

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

VoG and xenou,
Thanks for your replies. Since I am a newbie I am steering towards the least complicated option that VoG posted.

VoG, if it is not too much trouble can you explain what the "-1" is about. It seems to have no connection to -4 or -5 from the formulas I posted. Thanks

 

[VoG]

The Find statement locates the position of . in the filename. We subtract 1 since we don't want the . then use LEFT to extract the part that you want.