Search code examples
exceltrim

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

Solution

  • 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