C
C Brandt
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
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