1. Technology

Convert Text to Number in Excel

Use VBA in Excel 2003 and Excel 2007 to Convert Text Cells to Numbers

By

This is a logo for w:Microsoft Excel 2013.
Microsoft/Public Domain

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.

See More About
  1. About.com
  2. Technology
  3. Visual Basic
  4. Quick Tips
  5. Converting Text to Numbers Using VBA in Excel

©2014 About.com. All rights reserved.