software

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.
479811882A1 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
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:

www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for quot;Sub Trimall()quot;

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

Or you could use a formula in a different cell:

=--substitute(a1,char(160),quot;quot;)

The -- stuff converts text to numbers.

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

C Brandt wrote:
gt;
gt; Hi all:
gt;
gt; If you look at the two example numbers in the initial posting you will find
gt; that they are different. The first will not be treated as a number under any
gt; circumstances. I could not get any of the above tricks to work. I did
gt; follow some of the links Nick Otten provided (thank you) and now better
gt; understand what happened. I ran Len(A1) and the length was 10, as expected.
gt; ISNUMBER told me that it was not a number and even after removing the first
gt; character, it said it was not a number,but I could treat it as a number
gt; (except formating). I next used CODE(A1) and found out that the first
gt; character was an ASCII 160, non-breaking space. Since I will be passing this
gt; sheet on to others, I elected to not use macros and stuck with the simple
gt; following procedure.
gt; I put the following number in cell A1.
gt; Number as it was delivered to me. Any attempt to use it as a number resultes
gt; in a #VALUE!
gt; 479811882
gt;
gt; If I insert it into a formula (B1) like this:
gt; Right(A1,9)
gt; It leaves me with the number in B1:
gt; 479811882
gt;
gt; but I am unable to format it as a number, i.e.:479,811,882.00, but I can now
gt; use it as a number.
gt; If I put the formula B1*1 in cell C1, the result is now a true number and
gt; can be formated as such.
gt; 479811882
gt;
gt; A1 B1 C1
gt; Number Right(A1,9) B1*1
gt;
gt; Thanks all, for your responses. I am finding this site to be a great
gt; resource for information.
gt;
gt; Craig

--

Dave Peterson