- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2006 May
- Damaged Text to Numbers

Subject: | Damaged Text to Numbers |

Posted by: | C Brandt (cbran…@austin.rr.com) |

Date: | Mon, 15 May 2006 |

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

- Re: Damaged Text to Numbers posted by Dave Peterson on Mon, 15 May 2006