Remove Redundant Characters (Excel 2003)

Topic

New Reply

Greetings All

I used this formula to remove redundant characters from a Range
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”(“,””),”)”,””),”,”,””),”‘”,””),”-“,””))
it remove all except ” from the cells. What is need to place in the formula to remove ”

Thanks

regards,

Reply | Quote

Replies

Try

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"""",""),"(",""),")",""),",",""),"'",""),"-",""))

Note: this is a single formula even if your browser wraps the text.

Reply | Quote

Another option :

=MID(A1,ISNUMBER(FIND(“(“,A1))+1,SUMPRODUCT(–(CODE(RIGHT(A1,ROW(INDIRECT(“1:”&LEN(A1)))))>64)))

Regards
Bosco

Reply | Quote

appreciate if you can explain this interesting formula

thanks, francis

Reply | Quote

Your formula doesn’t handle non-alphabetic characters that are not at the end of a string. For example, (Power+Point) becomes Power+Poin, and “123 Go” becomes “1

Reply | Quote

1] Good catch, thanks Hans

2] Or try ………

=SUBSTITUTE(LEFT(A1,LOOKUP(” “,T(INDIRECT(MID(A1,ROW($1:$255),1)&255)),ROW($1:$255))),”(“,””)

or

=SUBSTITUTE(LEFT(A1,LOOKUP(1,N(INDIRECT(MID(A1,ROW($1:$255),1)&255)),ROW($1:$255))),”(“,””)

Regard
Bosco

Reply | Quote