Revenue Correction

Hi All,

I have the revenue in Column B. As I had mentioned in Column C is required.

1. If column B has only number. It is be as it its.
2. If column B has revenue with K example: 10989K This K should be removed and it has to be multiplied by 1000.
3. If column B has revenue with K example: 3125.31M This M should be removed and it has to be multiplied by 1000000
3. If column B has revenue with K example: 6545.79B This B should be removed and it has to be multiplied by 1000000000

Please find the attachment

You can use this formula in C2:

=IF(B2="","",IF(ISNUMBER(SEARCH("K",B2)),SUBSTITUTE(B2,"K","")*1000,IF(ISNUMBER(SEARCH("M",B2)),SUBSTITUTE(B2,"M","")*1000000,IF(ISNUMBER(SEARCH("B",B2)),SUBSTITUTE(B2,"B","")*1000000000,B2*1))))

then copy down as required.

Hope this helps.

Pete

If the cell contains 32k. It is not converting.

Hi
How about

Option Explicit
Sub test()
    Dim a As Variant
    Dim i As Integer
    Dim mtch, x, xx, y As Variant
    a = Application.Transpose(Cells(2, 1).Offset(, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row))
    With CreateObject("VBScript.Regexp")
        .Global = True
        .Pattern = "([+-]?(?=\.\d|\d)(?:\d+)?(?:\.?\d*)(?:[eE][+-]?\d+)?)|(\w)"
        For i = 1 To UBound(a)
            Set mtch = .Execute(a(i))
            xx = mtch.Count
            If xx < 2 Then GoTo 0
            x = mtch(1).submatches(1)
            y = mtch(0).submatches(0)
            Select Case x
            Case "K"
                a(i) = y * 1000
            Case "M"
                a(i) = y * 1000000
            Case "B"
                a(i) = y * 1000000000
            End Select
0       Next
    End With
    Cells(1, 1).Offset(1, 1).Resize(UBound(a)) = Application.Transpose(a)
End Sub

SEARCH is not case-sensitive, but SUBSTITUTE is, and if you have a lower-case k it will not be replaced. You can change the formula to this:

=IF(B2="","",IF(ISNUMBER(SEARCH("K",B2)),SUBSTITUTE(UPPER(B2),"K","")*1000,IF(ISNUMBER(SEARCH("M",B2)),SUBSTITUTE(B2,"M","")*1000000,IF(ISNUMBER(SEARCH("B",B2)),SUBSTITUTE(B2,"B","")*1000000000,B2*1))))

if you might have a mixture of K and k, and the same for the M and B terms, but if you will only ever have k then you can change K to k in the SUBSTITUTE term.

Hope this helps.

Pete

Here is another macro solution that you can try (assumed that data starts in cell B1 and output goes to Column C) ...

Sub RevenueCorrection()
  Dim X As Long, Data As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For X = 1 To UBound(Data)
    If Data(X, 1) Like "*[Kk]" Then
      Data(X, 1) = 1000 * Left(Data(X, 1), Len(Data(X, 1)) - 1)
    ElseIf Data(X, 1) Like "*[Mm]" Then
      Data(X, 1) = 1000000 * Left(Data(X, 1), Len(Data(X, 1)) - 1)
    ElseIf Data(X, 1) Like "*[Bb]" Then
      Data(X, 1) = 1000000000 * Left(Data(X, 1), Len(Data(X, 1)) - 1)
    End If
  Next
  Range("C1").Resize(UBound(Data)) = Data
End Sub

Another way amending directly the column B and working whatever upper or lower case letter :

PHP Code: 

Sub Demo1()
        Dim V, R&, W
    With [A1].CurrentRegion.Columns(2)
            V = .Value2
        For R = 2 To UBound(V)
            W = Application.Match(Right(V(R, 1), 1), [{"K","M","B"}], 0)
            If IsNumeric(W) Then V(R, 1) = Val(V(R, 1)) * 1000 ^ W
        Next
            .Value2 = V
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

An EZ formula in cell C2 : =IFERROR(LEFT(B2,LEN(B2)-1)*1000^MATCH(RIGHT(B2,1),{"K","M","B"},0),B2) …
Copy down, voilà !

If you really need to directly amend the column B via a VBA procedure so according to this formula :

PHP Code: 

Sub Demo2()
        Const F = "IFERROR(LEFT(#,LEN(#)-1)*1000^MATCH(RIGHT(#,1),{""K"",""M"",""B""},0),#)"
    With [A1].CurrentRegion.Columns(2)
        .Value2 = Evaluate(Replace(F, "#", .Address))
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !