Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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? |
#11
|
|||
|
|||
Only needed four instances and your solution worked just fine.
Thanks for your time! Cheryl B. "N Harkawat" wrote: 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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers Converted to Text - Last Digit Replaced by Zero
8 is the maximum... now, MY problem is that I have 28 posible substitutions
to make, plus another (or same:S) 28 fow lower case. (Im substituting letters for numbers... thats 56 posible substitutions!!!! How do i go with that!!????? "N Harkawat" wrote: 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? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers Converted to Text - Last Digit Replaced by Zero
On Thu, 7 Dec 2006 03:57:00 -0800, El Criollo
wrote: 8 is the maximum... now, MY problem is that I have 28 posible substitutions to make, plus another (or same:S) 28 fow lower case. (Im substituting letters for numbers... thats 56 posible substitutions!!!! How do i go with that!!????? You use a different solution than embedded Substitutions. I cannot find your original problem in this thread, and your description above is not sufficient to craft an efficient solution. Could you repost your problem with some details? --ron |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers Converted to Text - Last Digit Replaced by Zero
ok... say A (or lowercases) =65, B=66, C=67.... Z=90
Now I have a value in A1 of AB3D5 I want to convert it to 65663685 by replacing/substituting letters by its numeric value. Thanks Ron "Ron Rosenfeld" wrote: On Thu, 7 Dec 2006 03:57:00 -0800, El Criollo wrote: 8 is the maximum... now, MY problem is that I have 28 posible substitutions to make, plus another (or same:S) 28 fow lower case. (Im substituting letters for numbers... thats 56 posible substitutions!!!! How do i go with that!!????? You use a different solution than embedded Substitutions. I cannot find your original problem in this thread, and your description above is not sufficient to craft an efficient solution. Could you repost your problem with some details? --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers Converted to Text - Last Digit Replaced by Zero
On Thu, 7 Dec 2006 04:45:01 -0800, El Criollo
wrote: ok... say A (or lowercases) =65, B=66, C=67.... Z=90 Now I have a value in A1 of AB3D5 I want to convert it to 65663685 by replacing/substituting letters by its numeric value. Thanks Ron One easy way, assuming your strings are less than 256 characters long: Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ Then use this formula: =REGEX.SUBSTITUTE(UPPER(A1),"([A-Z])","=CODE(""[1]"")&""""") Notes: 1. The add-in is easily distributed with any workbooks. Be sure to check the option to install a morefunc menu option when you do the installation of this add-in. 2. The above converts any lower case letters to uppercase, as I believe you want both "a" and "A" represented by the code for "A". Let me know if this works for you. If your strings are longer than 255 characters, or if your company refuses to allow you to install an add-in, a similar routine can be written in VBA using Regular Expressions. --ron |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers Converted to Text - Last Digit Replaced by Zero
Didnt try it yet...
But...that would get rid of 28 posible substitutions by making sure allletters are now uppercase. But still need to substitute 28 posible uppercase letters by the corresponding number... "Ron Rosenfeld" wrote: On Thu, 7 Dec 2006 04:45:01 -0800, El Criollo wrote: ok... say A (or lowercases) =65, B=66, C=67.... Z=90 Now I have a value in A1 of AB3D5 I want to convert it to 65663685 by replacing/substituting letters by its numeric value. Thanks Ron One easy way, assuming your strings are less than 256 characters long: Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ Then use this formula: =REGEX.SUBSTITUTE(UPPER(A1),"([A-Z])","=CODE(""[1]"")&""""") Notes: 1. The add-in is easily distributed with any workbooks. Be sure to check the option to install a morefunc menu option when you do the installation of this add-in. 2. The above converts any lower case letters to uppercase, as I believe you want both "a" and "A" represented by the code for "A". Let me know if this works for you. If your strings are longer than 255 characters, or if your company refuses to allow you to install an add-in, a similar routine can be written in VBA using Regular Expressions. --ron |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers Converted to Text - Last Digit Replaced by Zero
On Thu, 7 Dec 2006 07:18:04 -0800, El Criollo
wrote: Didnt try it yet... But...that would get rid of 28 posible substitutions by making sure allletters are now uppercase. But still need to substitute 28 posible uppercase letters by the corresponding number... Huh -- I thought you only wanted to substitute codes for A-Z? At least in the alphabet with which I am familiar (American-English) that comprises only 26 letters. What are the two additional letters? What language? The function only substitutes numbers for the 26 upper case letters from A-Z. So you'll have to tell me which two letters are missing. I don't have any others on my keyboard. --ron |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers Converted to Text - Last Digit Replaced by Zero
The other two caracters are "-" and space (Have to eliminate, which i did
already), so yes, it is 26... but my head is still set to 28 :S "Ron Rosenfeld" wrote: On Thu, 7 Dec 2006 07:18:04 -0800, El Criollo wrote: Didnt try it yet... But...that would get rid of 28 posible substitutions by making sure allletters are now uppercase. But still need to substitute 28 posible uppercase letters by the corresponding number... Huh -- I thought you only wanted to substitute codes for A-Z? At least in the alphabet with which I am familiar (American-English) that comprises only 26 letters. What are the two additional letters? What language? The function only substitutes numbers for the 26 upper case letters from A-Z. So you'll have to tell me which two letters are missing. I don't have any others on my keyboard. --ron |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers Converted to Text - Last Digit Replaced by Zero
On Fri, 8 Dec 2006 01:20:01 -0800, El Criollo
wrote: The other two caracters are "-" and space (Have to eliminate, which i did already), so yes, it is 26... but my head is still set to 28 :S If that is what you were concerned about, then not only have you not tried the function, but you also don't understand it. As provided, it does everything you specified in your original post. --ron |
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) |