Numbers Converted to Text - Last Digit Replaced by Zero

[Cheryl B.]

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]

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.]

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,

 

[Ron Rosenfeld]

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

 

[Harlan Grove]

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.

 

[N Harkawat]

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.]

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.

 

[Cheryl B.]

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

 

[Cheryl B.]

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

 

[N Harkawat]

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(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"
",""),"-",""),"/",""),"k",""),"n",""),"o",""),"q",""),"w","")

 

[Cheryl B.]

Only needed four instances and your solution worked just fine.
Thanks for your time!
Cheryl B.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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