Home 
Search 
Today's Posts 
#1




Numbers Converted to Text  Last Digit Replaced by Zero
I have an account number of 941 4861072 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




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 4861072 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




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 4861072 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




On Thu, 26 May 2005 10:02:01 0700, Cheryl B.
wrote: I have an account number of 941 4861072 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




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 "9999 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 reenter them. For comparison, in both OpenOffice Calc and Gnumeric, if A1 has number format Text and initially contains "9999 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




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 4861072 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




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 4861072 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




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 "9999 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 reenter them. For comparison, in both OpenOffice Calc and Gnumeric, if A1 has number format Text and initially contains "9999 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




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 4861072 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




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 4861072 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 
Display Modes  


Similar Threads  
Thread  Forum  
How to convert Excel imported numbers from text to numbers?  Excel Discussion (Misc queries)  
Converting Numbers to Text properly  Excel Discussion (Misc queries)  
excel  numbers as text  New Users to Excel  
Convert text to numbers  Excel Discussion (Misc queries)  
Sorting when some numbers have a text suffix  Excel Discussion (Misc queries) 