Converting non-uniform Excel data into uniform data by VBA

[Yup]

Hi, I had a question.
I wanted to convert non-uniform data in single Excel column to uniform data into another column.
Example- I have an Excel column containing values as 345.12 million, 128.56 billion, 2.67 trillion and I would like to convert this non-uniform data into a uniform way using VBA i.e. the expected outcome should either be

0.34512 billion, 128.56 billion, 2670 billion

OR

345120000, 128560000000, 2670000000000.

Is there any way it is possible ?

Thank you in advance.

 

[mohadin]

Hi
What about

Sub test()
    Dim a, x, y
    Dim i&
    Dim m As Object
    a = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
    x = Application.Index(Array("million", "billion", "trillion"), 0, 0)
    y = Application.Index(Array("1000000", "1000000000", "1000000000000"), 0, 0)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[\d+\.*]+|[a-zA-Z]+"
        For i = 1 To UBound(a)
            Set m = .Execute(a(i, 1))
            a(i, 1) = m(0) * y(Application.Match(m(1), x, 0))
        Next
    End With
    Cells(2, 2).Resize(UBound(a)) = a
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

If the only options are million, billion and trillion, try this

Sub Convert_Numbers()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "0"
    .Value = Evaluate(Replace("left(#,FIND("" "",#))*if(right(#,7)=""million"",10^6,if(right(#,7)=""billion"",10^9,10^12))", "#", .Offset(, -1).Address))
  End With
End Sub

My sample data (col A) and results (col B)

Yup.xlsm
	A	B
1
2	345.12 million	345120000
3	128.56 billion	128560000000
4	2.67 trillion	2670000000000
Sheet3