Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 26th 05, 06:02 PM
Cheryl B.
 
Posts: n/a
Default Numbers Converted to Text - Last Digit Replaced by Zero

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?

  #2   Report Post  
Old May 26th 05, 06:09 PM
N Harkawat
 
Posts: n/a
Default

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?



  #3   Report Post  
Old May 26th 05, 06:41 PM
Cheryl B.
 
Posts: n/a
Default

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?




  #4   Report Post  
Old May 26th 05, 06:48 PM
Ron Rosenfeld
 
Posts: n/a
Default

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
  #5   Report Post  
Old May 26th 05, 06:53 PM
Harlan Grove
 
Posts: n/a
Default

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.



  #6   Report Post  
Old May 26th 05, 07:17 PM
N Harkawat
 
Posts: n/a
Default

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?






  #7   Report Post  
Old May 26th 05, 07:38 PM
Cheryl B.
 
Posts: n/a
Default

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?






  #8   Report Post  
Old May 26th 05, 07:38 PM
Cheryl B.
 
Posts: n/a
Default

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.


  #9   Report Post  
Old May 26th 05, 07:40 PM
Cheryl B.
 
Posts: n/a
Default

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

  #10   Report Post  
Old May 26th 05, 07:45 PM
N Harkawat
 
Posts: n/a
Default

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?










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert Excel imported numbers from text to numbers? Alden Excel Discussion (Misc queries) 9 April 1st 05 09:51 PM
Converting Numbers to Text properly Shirley Munro Excel Discussion (Misc queries) 1 February 16th 05 03:01 PM
excel - numbers as text Thuferhawat New Users to Excel 12 January 24th 05 09:29 PM
Convert text to numbers gennario Excel Discussion (Misc queries) 6 January 10th 05 11:56 PM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 10:19 PM


All times are GMT +1. The time now is 12:53 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017