CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Ben Dill
Ben Dill

Reputation: 21

remove a character from the end of a text string only if it is there in excel

I have a list of file names in excel I need to Match with another list. Some of the file names contain extra characters that need to be removed first though. I have a formula that will remove special characters and spaces from the file names;

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E8,"_",""),"-",""),".","")," ","")

But some of the file names contain an extra 1 at the end I need to remove, please see example;

If anyone could give me a Formula solution that strips out the above special characters and the 1 at the end of the filename that would be great.

Bonus credit if you can also strip out the 20 from the STC files as well to output as STCSRA120114 instead of STCSRA12012014

Edit: For clarification, final result would ideally look like this;

Thanks, Ben

Upvotes: 2

Views: 380

Answers (1)

pnuts
pnuts

Reputation: 59475

Maybe: 

=LEFT(A1,LEN(A1)-IF(RIGHT(A1,1)="1",1,0))

(Replace the first two instances of A1 above with a suitable version of your SUBSTITUTE formula, and the last with E8).

With substitution: 

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E8,"_",""),"-",""),".","")," ",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E8,"_",""),"-",""),".","")," ",""))-IF(RIGHT(E8,1)="1",1,0))

A sightly shorter version of the A1 one: 

=LEFT(A1,LEN(A1)-(RIGHT(A1)="1"))

Upvotes: 1

Related Questions