Convert abbreviated data to numbers [Excel VBA] | k,lacs,millions to numeric data

June 14, 2021

by SRINI S

Excel VBA macro to format abbreviated data to numbers. For example, 1.1k to 1100 (k to thousands), 2.1lacs to 210000 and 1mil to 1000000.

The advantage of this macro is that you don’t need to format each number. The macro detects the text (k, lacs, mil and cr) and does the math.

Before you run this macro, write “END” (without “) after the last used cell in the column. So the macro stops execution when it reaches the “END” written cell. So, it executes from the selected cell in the column until it reads “END” cell.

Sub convertKlacsToNumbers()
While Not ActiveCell.Value = "END"
    If InStr(1, ActiveCell.Value, "k", vbTextCompare) Then
    ActiveCell.Value = Replace(ActiveCell.Value, "k", "")
    ActiveCell.Value = ActiveCell.Value * 1000
    ActiveCell.Offset(1, 0).Select
    ElseIf InStr(1, ActiveCell.Value, "lacs", vbTextCompare) Then
    ActiveCell.Value = Replace(ActiveCell.Value, "lacs", "")
    ActiveCell.Value = ActiveCell.Value * 100000
    ActiveCell.Offset(1, 0).Select
    ElseIf InStr(1, ActiveCell.Value, "cr", vbTextCompare) Then
    ActiveCell.Value = Replace(ActiveCell.Value, "cr", "")
    ActiveCell.Value = ActiveCell.Value * 10000000
    ActiveCell.Offset(1, 0).Select
    ElseIf InStr(1, ActiveCell.Value, "mil", vbTextCompare) Then
    ActiveCell.Value = Replace(ActiveCell.Value, "mil", "")
    ActiveCell.Value = ActiveCell.Value * 1000000
    ActiveCell.Offset(1, 0).Select
    Else
    ActiveCell.Offset(1, 0).Select
    End If
Wend
End Sub