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

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:

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

None

Damaged Text to Numbers posted by C Brandt on Mon, 15 May 2006