Sunday, October 20, 2019

Converting Text to Numbers Using VBA in Excel

Converting Text to Numbers Using VBA in Excel 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 doesnt 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 doesnt 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 doesnt 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 Its not rocket science. I cant understand why so many pages have it wrong.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.