|Subject:||Re: Damaged Text to Numbers|
|Posted by:||Dave Peterson (peters…@verizonXSPAM.net)|
|Date:||Mon, 15 May 2006|
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:
(look for "Sub Trimall()"
If you're new to macros, you may want to read David McRitchie's intro at:
Or you could use a formula in a different cell:
The -- stuff converts text to numbers.
Drag down the column and convert to values. Then delete the original
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!
> If I insert it into a formula (B1) like this:
> It leaves me with the number in B1:
> 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.
> 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.
Damaged Text to Numbers posted by C Brandt on Mon, 15 May 2006