Number stored as text changed with "Convert to number" in VB

[Ed Mah]

Hi. So I'm downloading data from an external application into Excel. The problem is a lot of the data is 'Number stored as text'.

Using VB, how can I 'Convert to number'. I have hundreds of instances of this problem so manual corrections are impractical.

 

[Joe4]

You can use Text to Columns on each of the columns to convert those "numbers entered as text" to "numbers".

The steps manual are:
1. Go to the Data menu
2. Select "Text to Columns"
3. Click "Finish"

But you could use the Macro Recorder to record these steps, and then edit the code to make it more dynamic.

You can only do "Text to Columns" on one column at a time, but could automate looping through multiple columns pretty easy with VBA.

An alternative trick may be to temporarily save the file as a CSV, then close it.
Then open the CSV in Excel and save it as an Excel file.
That should convert those values. However, it could potentially convert other values too, depending on what kind of data you have.

 

[Ruddles]

Does this do it?

Sub convert2number()

  Dim oCell As Range

  For Each oCell In ActiveSheet.UsedRange
    If IsNumeric(oCell) Then oCell = oCell.Value
  Next

End Sub

 

[Ed Mah]

Joe, the 'Text to columns' worked. Thanks.

 

[Joe4]

You are welcome.
Glad we were able to help!