Question: How do I convert cells filled with character numbers to numeric values so I can use the values in Excel math formulas.
I recently had to add a column of numbers in Excel that were copied and pasted from a table in a web page. Because the numbers are represented by text in the web page (that is, the number "10" is actually "Hex 3130"), a Sum function for the column simply results in a zero value.
You can find a lot of web pages (including Microsoft pages) that simply give you advice that doesn't work. For example, this page ...
http://support.microsoft.com/kb/291047
... gives you seven methods. The only one that actually works is to retype the value manually. (Gee, thanks, Microsoft. I never would have thought of that.) The most common solution I found on other pages is to Copy the cells and then use Paste Special to paste the Value. That doesn't work either. (Tested on Excel 2003 and Excel 2007.)
The Microsoft page provides a VBA Macro to do the job ("Method 6"):
Sub Enter_Values()
For Each xCell In Selection
xCell.Value = xCell.Value
Next xCell
End Sub
It doesn't work either, but all you have to do is make one change and it does work:
For Each xCell In Selection
xCell.Value = CDec(xCell.Value)
Next xCell
It's not rocket science. I can't understand why so many pages have it wrong.
