Convert Text to Number within a column

[krg220]

Hi,

I have a column in which I have the revenue of numerous companies, but they show up in shorthand form "$491.80M" to "$2.06B", how would I go about converting these to actual numbers "491,000,000" and "2,060,000,000"?

In the past I have just filtered out the M or B for these values then multiplied the value by 1,000,000 or 1,000,000,000 respectively. But I would like to create a macro for this as it is very time consuming.

i was thinking of a formula along the lines of:

Dim Reng As Range
Dim h As Range
ColNum = Cells.Find(What:="Revenue").Column
LastRow = Cells(Rows.Count, ColNum).End(xlUp).Row
Set Reng = Range(Cells(1, ColNum), Cells(LastRow, ColNum))
For Each h In Reng
If h.Value contains "M" Then
h.Value = "mulitply by 1000000"
End If
Next h

^ I know the above is wrong, but any help would be greatly appreciated!

 

[cameron.richens]

why not just create a macro to insert the formula you already know how to create.

this is an example of mine (replace your formula with mine and it will copy all the way down to the end of your column)


Sub CopyDown()

Range("K19").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(CONCATENATE(RC4,RC2),Data!C1:C5,4,FALSE)),"""",VLOOKUP(CONCATENATE(RC4,RC2),Data!C1:C5,4,FALSE))"

Selection.Copy
ActiveCell.Offset(1, 0).Select
Set upperLeftcell = ActiveCell
ActiveCell.Offset(0, -1).Select
Cells(65536, ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(0, 1).Select
Set lowerRightcell = ActiveCell
Range(upperLeftcell, lowerRightcell).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub