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?

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?

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

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.

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?

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.

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

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?

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?

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?

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

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

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

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

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

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

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