Pasting numbers doesn't format them as numbers

[Guest]

Howdy

We have a web app that we run reports from. THe reports are just numerical data in table format. We then put this information into Excel to do totally, charts, etc. THe problem is that when we highlight the data in IE5.5 and paste it into Excel 2k, while everything looks like it works fine, none of the numbers can be totalled or done math with. Even if we select the cells and manually go to Format / Cells / Number, if still doesn't do anything when we try to do a summation or any other calculations

Anyone have any idea why it would do this? We have a sort of workaround by copying from IE, pasting to Wordpad, then copying from Wordpad and pasting to Excel but that's just annoying. If there a different way to do the paste directly into Excel or a way to format the data after it gets there, that would be the best

Thanks

 

[JE McGimpsey]

Your data is being pasted in as text. To coerce text values to numbers,
copy an empty cell, select your numbers and choose Edit/Paste Special,
selecting the Values and Add radio buttons. Click OK.

 

[Guest]

So do you mean paste empty cells over the top of the cells with the data? Or choose the Values button when I do the intial paste from IE to Excel?

 

[JE McGimpsey]

Choose the Values and Add radio buttons in the Paste Special dialog.

 

[Dave Peterson]

Since it came from MSIE:

Just in case you have some of those pesky HTML non-breaking spaces in your data:

You could use David McRitchie's Trimall macro:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

David also has instructions on how to install a macro and run it at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm