Copying numbers from HTML table into Excel: How to make them numbers, not text?

I occasionally have to copy a table from a webpage into Excel. When I do I find that I am often unable to manipulate the numbers as numbers. Right clicking on one of these cells (for example A1) and selecting "Format Cells..." I can see that "General" format is selected. I change this to "Number", select "0" for decimal places and check the "Use 1000 separator" box. Select "Okay" to close the window. However, now when I go to a blank cell (B1) and type =A1/1 I get "#VALUE!" error message.

What is the easiest way to resolve this?

Thanks. In the past I'd solved this w/a variety of Rube Goldberg methods, (=SUBSTITUTE(A1," ","") but none seem to completely do the trick. I still end up doing a lot of hand entering.

You often get the non-breaking space character (code = 160) when copying data from HTML tables. A quick way of getting rid of them is to highlight all the data then do CTRL-H (Find & Replace) and:

Find what: ALT-0160
Replace with: (leave blank)
Click Replace All

where ALT-0160 mean to hold down the Alt key and type the digits 0160 on the numeric keypad, then release Alt. Do not use the number keys running across the top of the keyboard. That will usually convert the text-numbers to proper numbers.

Hope this helps.

Pete

Originally Posted by Pete_UK

You often get the non-breaking space character (code = 160) when copying data from HTML tables. A quick way of getting rid of them is to highlight all the data then do CTRL-H (Find & Replace) and:

Find what: ALT-0160
Replace with: (leave blank)
Click Replace All

where ALT-0160 mean to hold down the Alt key and type the digits 0160 on the numeric keypad, then release Alt. Do not use the number keys running across the top of the keyboard. That will usually convert the text-numbers to proper numbers.

Hope this helps.

Pete

Thanks Pete. That nailed it AFTER I disengaged the keyboard NUM LOCK key. Best regards.