Revenue Correction

[sathishkm]

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

[Pete_UK]

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

[sathishkm]

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

[mohadin]

Hi
How about

Please Login or Register  to view this content.

[Pete_UK]

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

[Rick Rothstein]

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

Please Login or Register  to view this content.

[Marc L]

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

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 » !

[Rick Rothstein]

IGNORE... Did not work as I thought it would.

[Rick Rothstein]

Instead of all that code I posted in Message #6, how about this more compact macro (it does not use a loop)...

Please Login or Register  to view this content.

Note: This code puts its output in Column C... if you want to overwrite the original data in Column B. simply remove the .Offset(,1) that I highlighted in blue.

[Marc L]

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 :

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 » !