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?

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?

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?

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?

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?

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?

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," ",""),"-","")
....

This works, and it may be the most efficient way to do this. The OP's
problem is Excel's limit of 15 decimal digits of precision. The OP's
sample string had 16 decimal numerals.

I came across what I consider a bug when playing with this. Even if the
cell had the number format Text, if there were no leading apostrophe,
Excel's Edit Replace converts text strings of decimal numerals to
numbers. If I give cell A1 the number format Text, enter "99-99 99" in
it (without the double quotes), then replace the dash with nothing then
the space with nothing, Excel converts this into a number even though
the cell has number format Text. So it appears Edit Replace doesn't
simply change cell contents and re-enter them.

For comparison, in both OpenOffice Calc and Gnumeric, if A1 has number
format Text and initially contains "99-99 99" (w/o the double quotes),
replacing the dash and space with nothing leaves the result text.

Just more evidence that, in the peripheral areas, Excel is one of the
most sloppily programmed pieces of software currently available.

Thanks for your insight, Harlan ... at least I know that I'm not crazy. :)
Cheryl B.

"Harlan Grove" wrote:

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," ",""),"-","")
....

This works, and it may be the most efficient way to do this. The OP's
problem is Excel's limit of 15 decimal digits of precision. The OP's
sample string had 16 decimal numerals.

I came across what I consider a bug when playing with this. Even if the
cell had the number format Text, if there were no leading apostrophe,
Excel's Edit Replace converts text strings of decimal numerals to
numbers. If I give cell A1 the number format Text, enter "99-99 99" in
it (without the double quotes), then replace the dash with nothing then
the space with nothing, Excel converts this into a number even though
the cell has number format Text. So it appears Edit Replace doesn't
simply change cell contents and re-enter them.

For comparison, in both OpenOffice Calc and Gnumeric, if A1 has number
format Text and initially contains "99-99 99" (w/o the double quotes),
replacing the dash and space with nothing leaves the result text.

Just more evidence that, in the peripheral areas, Excel is one of the
most sloppily programmed pieces of software currently available.

On Thu, 26 May 2005 10:02:01 -0700, Cheryl B.
wrote:

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?

When you are doing your find/replace and winding up with digits, excel converts
it to a number. Since Excel numbers can only have 15 digits, the 16th digit
gets changed to a zero.

So you need to return a text string and that requires a worksheet function:

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


--ron

Thanks, Ron - that's exactly what N Harkawat wrote ... and works just fine.
Thanks for your time to confirm! Cheryl B.

"Ron Rosenfeld" wrote:

On Thu, 26 May 2005 10:02:01 -0700, Cheryl B.
wrote:

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?

When you are doing your find/replace and winding up with digits, excel converts
it to a number. Since Excel numbers can only have 15 digits, the 16th digit
gets changed to a zero.

So you need to return a text string and that requires a worksheet function:

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


--ron