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

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
example of format abbreviated data to numerical data in excel VBA
example: showing the conversion before and after running the macro

About the Author

SRINI S

A passionate blogger. Love to share solutions and best practices on wordpress hosting, issues and fixes, excel VBA macros and other apps

Leave a Reply

Your email address will not be published.