CODEWITHSUNDEEP
exceltrim
Luis
Luis

Reputation: 139

Remove specific characters from a string in a cell

I am trying to have a formula that will have certain characters removed from a string. For Example. CC200-M1-02-1-1 (string) I want this to come out like this, CC200021. So the following characters are removed (-,m,1,-,-,-,1). I have tried the lens right but this will only remove the last characters. Any suggestions?

CC200-M1-02-1-1

CC230-M1-02-1-1

CC250-M1-02-1-1

CS005-MW-14-1-1

LG100-M0-14-1-1

LG150-M0-14-1-1

LG220-M0-14-1-1

LG230-M0-14-1-1

LX150-M0-14-1-1

Upvotes: 0

Views: 1804

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

If it is a formula you want then use this:

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",999)),1,999) )&TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",999)),2*999,999) )&TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",999)),3*999,999) )

enter image description here

Upvotes: 2

Related Questions