Training By Himanshu

Tag: Excel Convert Number to Words

  • How to Convert Numbers to Words in Indian Rupees in Excel (With VBA)

    💰 How to Convert Numbers to Words in Indian Rupees in Excel

    Excel doesn’t have a built-in function to convert numbers to words (like “12345” → “Twelve Thousand Three Hundred Forty-Five”) — especially not in the Indian currency format like “Rupees Twelve Thousand Three Hundred Forty-Five Only”.

    But you can achieve this using a custom VBA function.

    ✅ Step-by-Step Guide: Convert Numbers to Words in Indian Rupees

    🧩 Step 1: Open VBA Editor

    1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
    2. Go to Insert > Module.

    ✍️ Step 2: Paste the VBA Code

    Paste the following code into the module window:

    Function ConvertToRupees(ByVal MyNumber)
    Dim Units As String
    Dim SubUnits As String
    Dim TempStr As String
    Dim Rupees As String
    Dim Paise As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim Place(9) As String
    Dim NumDigit As Integer
    Dim Digit As Integer
    Dim MyNum As String
    Dim DecimalPart As String
    
    Place(2) = " Thousand "
    Place(3) = " Lakh "
    Place(4) = " Crore "
    
    ' Convert MyNumber to string and find position of decimal
    MyNumber = Trim(Str(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")
    
    ' Split rupees and paise
    If DecimalPlace > 0 Then
        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
        If Count = 1 Then
            TempStr = GetHundreds(Right(MyNumber, 3))
            If Len(MyNumber) > 3 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 3)
            Else
                MyNumber = ""
            End If
        Else
            TempStr = GetTens(Right(MyNumber, 2))
            If Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        End If
        If TempStr <> "" Then Rupees = TempStr & Place(Count) & Rupees
        Count = Count + 1
    Loop

    ConvertToRupees = "Rupees " & Rupees & IIf(Paise <> "", " and " & Paise & " Paise", "") & " Only"
End Function

Private Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function

Private Function GetTens(TensText)
    Dim Result As String
    If Val(Left(TensText, 1)) = 1 Then
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
        Result = Result & GetDigit(Right(TensText, 1))
    End If
    GetTens = Result
End Function

Private Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function

    🧪 Step 3: Use the Function in Excel

    After saving the VBA code:

    1. Go back to your Excel sheet.
    2. In any cell, enter:
    =ConvertToRupees(123456.78)
or
=ConvertToRupees(A2)

*** Here A2 Cell contains numbers

    Output:
    Rupees One Lakh Twenty Three Thousand Four Hundred Fifty Six and Seventy Eight Paise Only

    📌 Notes:

    • Works with Indian Numbering System (Thousand, Lakh, Crore).
    • You must enable macros to use this function.
    • Use it in billing templates, invoices, payment receipts, etc.

    📣 Learn More Excel Automation Like This!

    🎓 Join My Excel Mastery Course
    Includes advanced functions, automation using VBA, and business templates with Indian formats.

    ✅ Here is your ready-to-use Excel template to convert numbers to words in Indian Rupees:

    Convert_Numbers_to_Words_TemplateDownload

    📌 Important:

    This file includes:

    • Sample amounts in column A
    • A formula in column B using =ConvertToRupees(...)
    1. This is Macro enabled file. So you may get the warning. Just enabled the content if you are getting message in yellow and you are good to go.

    Breakdown of Excel VBA Function

    🔹 OVERVIEW OF THE MAIN FUNCTION: ConvertToRupees(ByVal MyNumber)

    This is the main function you call. It takes a numeric value (like 12345.50) and converts it into a full “Rupees … and … Paise” string.

    🔸 STEP 1: Variable Declarations

    vbaCopyEditDim Units As String, SubUnits As String, TempStr As String
Dim Rupees As String, Paise As String
Dim DecimalPlace As Integer, Count As Integer
Dim Place(9) As String
    • Rupees: Final string for rupee part.
    • Paise: Final string for decimal part.
    • Place(): Array for Indian numbering system—Thousand, Lakh, Crore, etc.
    • Count: Keeps track of digit positions (units, thousands, lakhs…).
    • TempStr: Stores interim word values as they’re built.
    vbaCopyEditPlace(2) = " Thousand "
Place(3) = " Lakh "
Place(4) = " Crore "

    🔸 STEP 2: Handle Decimal and Integer Split

    vbaCopyEditMyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
    • Converts the number to string, and finds the position of the decimal point (if it exists).
    • For example, 12345.50 becomes:
      • Rupee part: 12345
      • Paise part: 50
    vbaCopyEditIf DecimalPlace > 0 Then
    Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
    • Extracts the paise part using the GetTens function (explained later).
    • Ensures at least 2 digits by appending "00" and trimming.

    🔸 STEP 3: Main Loop to Build Words for Rupee Part

    vbaCopyEditCount = 1
Do While MyNumber <> ""
    • Processes the rupee part in blocks of 3 or 2 digits, depending on the Indian number system.
    vbaCopyEditIf Count = 1 Then
    TempStr = GetHundreds(Right(MyNumber, 3))
    • First time, take the last 3 digits (i.e., units, tens, hundreds).
    vbaCopyEditElse
    TempStr = GetTens(Right(MyNumber, 2))
    • From second iteration onwards, take 2 digits at a time (i.e., thousands, lakhs, crores).
    vbaCopyEditIf TempStr <> "" Then Rupees = TempStr & Place(Count) & Rupees
Count = Count + 1
    • Adds the word form to Rupees, prepending appropriate place name.

    🔸 STEP 4: Final Formatting

    vbaCopyEditConvertToRupees = "Rupees " & Rupees & IIf(Paise <> "", " and " & Paise & " Paise", "") & " Only"
    • Constructs the final string like:
      “Rupees Twelve Thousand Three Hundred Forty Five and Fifty Paise Only”

    🔹 SUPPORTING FUNCTIONS

    🔸 GetHundreds(MyNumber)

    • Converts a 3-digit number into words.
    • Example: "345" → "Three Hundred Forty Five"
    vbaCopyEditMyNumber = Right("000" & MyNumber, 3)
    • Pads numbers like "45" to "045" for consistent processing.
    vbCopyEditIf Mid(MyNumber, 1, 1) <> "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    • If the hundred’s digit is not zero, convert it (e.g., 3 → “Three Hundred”).
    vbaCopyEditIf Mid(MyNumber, 2, 1) <> "0" Then
    Result = Result & GetTens(Mid(MyNumber, 2))
Else
    Result = Result & GetDigit(Mid(MyNumber, 3))
End If
    • Handles the remaining 2 digits using GetTens.

    🔸 GetTens(TensText)

    • Converts 2-digit numbers to words.
    • Example: "45" → "Forty Five", "19" → "Nineteen"
    vbaCopyEditIf Val(Left(TensText, 1)) = 1 Then
    Select Case Val(TensText)
        Case 10: Result = "Ten"
        Case 11: Result = "Eleven"
        ...
    • Handles special case of numbers from 10 to 19.
    vbaCopyEditElse
    Select Case Val(Left(TensText, 1))
        Case 2: Result = "Twenty "
        Case 3: Result = "Thirty "
        ...
    • Handles the tens place (20, 30, 40…).
    vbaCopyEditResult = Result & GetDigit(Right(TensText, 1))
    • Adds the unit place digit (e.g., 4 in 34).

    🔸 GetDigit(Digit)

    • Converts a single digit to its word form.
    • E.g., 1 → "One", 9 → "Nine"

    🔚 SUMMARY FLOW

    mathematicaCopyEditInput → "12345.50"
↓
Split to "12345" (Rupees) and "50" (Paise)
↓
Break into 3-2-2: 12 (Thousand), 345
↓
→ "Twelve Thousand Three Hundred Forty Five"
→ "Fifty Paise"
↓
Output → "Rupees Twelve Thousand Three Hundred Forty Five and Fifty Paise Only"

    Best selling products