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"
^ I know the above is wrong, but any help would be greatly appreciated!