Imported Numbers not recognized as numbers

[rlaw68]

Hi there -- I've seen this problem posted a lot before, however
haven't yet seen a solution posted that works for my situation.

This applies to Office XP...

So I copy a column of data (numbers) from a website and paste it int
Excel, but the numbers are not recognized as numeric values (can't su
them, etc.)

I have tried all of the "easy" solutions -- changing their format t
general or number, copying a 1 from another cell in a clean workboo
and using the paste special/multiply (or a 0 and paste special/add
function...I've tried the text to columns thing -- basically have trie
everything in the MS KB or on this site, but nothing works short o
retyping the value in each cell, which is a bummer since I've got abou
3,000 cells I'm working with.

Any help would be appreciated.

Thanks,
Richar

 

[liddlem]

Hi Richard
Have you tried using the "VALUE()" function.

I.E. Cell A1 contains the string '7
Cell B1 contains the formula "+VALUE(A1)"

Now add up (or whatever else you wish to do) the VALUES in column B

 

[Roger Govier]

Hi

Sometimes data copied from a web site can have the non breaking space
Char(160) included, causing the number to dehave as text rather than
numeric.

David McRitchie has a macro solution. Take a look at the TRIMALL function at
http://www.mvps.org/dmcritchie/excel/trimall.htm

 

[Jerry W. Lewis]

That address should probably be

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

However, it is easier to just do a search and replace to replace
Alt-0160 with nothing. If the cells are formatted as numeric or
General, then the resulting values will be reinterpreted as numbers
without further ado.

Jerry

 

[rlaw68]

Thanks guys -- the TrimAll Macro did the trick. I wonder if there's an
way to add the TrimAll Macro to the Paste Special menu so that you hav
a Paste Special --> Paste as Numbers or something..