Keeping looping them within each substitute HOWEVER i am not sure as to how
many it can loop. May be try and let us know
For instance
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"
",""),"-",""),"/",""),"k",""),"n",""),"o",""),"q",""),"w","")
"Cheryl B." wrote in message
...
That's exactly what I did ... just wanted to see if there was another way
to
do it all in one fell swoop. Your initial formula took care of the
painful
editing and find/replace works for the rest of it.
Thanks again, N Harkawat !
Cheryl B.
"N Harkawat" wrote:
you could do find and replace for all other characters,
So long as "-" or a space remains in the string excel thinks it as text
and
not numbers and hence will not covert the 16th digit to 0
After replacing them all you may want to use the formula offered
"Cheryl B." wrote in message
...
Thank, N Harkawat ... that did work for two characters. I've since
discovered slashes and dots/periods - and may find more.
Tried editing to mimick the syntax but errors out. Is there a secret
you
can share?
Thanks,
"N Harkawat" wrote:
If space and "-" are the only 2 characters in your string then use
this
formula instead in another column and copy it down
=SUBSTITUTE(SUBSTITUTE(A1," ",""),"-","")
Then you can copy and paste special values to eliminate the formulas
"Cheryl B." wrote in message
...
I have an account number of 941 486-1072 041025 in text format.
Need to remove spaces and dashes for a continuous data string.
Tried using find/replace to remove spaces and dashes and it works -
*almost*
The problem is that the last digit of 5 is replaced by a 0 (zero)
and
shows
as
9414861072041020
Any suggestions? Other than manually editing each cell, that is?
|