Damaged Text to Numbers
 

Hi all:

If you look at the two example numbers in the initial posting you will find
that they are different. The first will not be treated as a number under any
circumstances. I could not get any of the above tricks to work. I did
follow some of the links Nick Otten provided (thank you) and now better
understand what happened. I ran Len(A1) and the length was 10, as expected.
ISNUMBER told me that it was not a number and even after removing the first
character, it said it was not a number,but I could treat it as a number
(except formating). I next used CODE(A1) and found out that the first
character was an ASCII 160, non-breaking space. Since I will be passing this
sheet on to others, I elected to not use macros and stuck with the simple
following procedure.
I put the following number in cell A1.
Number as it was delivered to me. Any attempt to use it as a number resultes
in a #VALUE!
479811882

If I insert it into a formula (B1) like this:
Right(A1,9)
It leaves me with the number in B1:
479811882

but I am unable to format it as a number, i.e.:479,811,882.00, but I can now
use it as a number.
If I put the formula B1*1 in cell C1, the result is now a true number and
can be formated as such.
479811882


A1 B1 C1
Number Right(A1,9) B1*1

Thanks all, for your responses. I am finding this site to be a great
resource for information.

Craig

Damaged Text to Numbers
 

I'm not sure many people have access to the original thread, so it's difficult
to know what links you tried.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Or you could use a formula in a different cell:

=--substitute(a1,char(160),"")

The -- stuff converts text to numbers.

Drag down the column and convert to values. Then delete the original
non-numeric column.

C Brandt wrote:

Hi all:

If you look at the two example numbers in the initial posting you will find
that they are different. The first will not be treated as a number under any
circumstances. I could not get any of the above tricks to work. I did
follow some of the links Nick Otten provided (thank you) and now better
understand what happened. I ran Len(A1) and the length was 10, as expected.
ISNUMBER told me that it was not a number and even after removing the first
character, it said it was not a number,but I could treat it as a number
(except formating). I next used CODE(A1) and found out that the first
character was an ASCII 160, non-breaking space. Since I will be passing this
sheet on to others, I elected to not use macros and stuck with the simple
following procedure.
I put the following number in cell A1.
Number as it was delivered to me. Any attempt to use it as a number resultes
in a #VALUE!
479811882

If I insert it into a formula (B1) like this:
Right(A1,9)
It leaves me with the number in B1:
479811882

but I am unable to format it as a number, i.e.:479,811,882.00, but I can now
use it as a number.
If I put the formula B1*1 in cell C1, the result is now a true number and
can be formated as such.
479811882

A1 B1 C1
Number Right(A1,9) B1*1

Thanks all, for your responses. I am finding this site to be a great
resource for information.

Craig

--

Dave Peterson