# Convert Text to Number within a column

#### krg220

##### New Member
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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:

Replies
5
Views
250
Replies
1
Views
141
Replies
5
Views
2K
Replies
4
Views
219
Replies
1
Views
473

1,217,328
Messages
6,135,917
Members
449,972
Latest member
Natejack

### 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.

### Which adblocker are you using?

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

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