Convert Text to Number within a column

krg220

New Member
Joined
Mar 7, 2011
Messages
2
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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

cameron.richens

New Member
Joined
Nov 12, 2010
Messages
24
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
 
Last edited:

Forum statistics

Threads
1,144,694
Messages
5,725,803
Members
422,640
Latest member
KazPL

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top