VBA Code for Converting Numeric Indian Rupees and Paise to Words

Here you will learn the steps of creating a formula in Excel to convert number to words for Indian currency “rupees:” and “paise”. You can also copy the required VBA code from below.

Steps to Follow

1. Copy the VBA code from below.
2. Open MS Excel.
3. Click on “Developer” tab.
4. Click on “Visual Basics.” (This opens VBA window)
5. In the VBA window, click on “Insert” tab.
6. Click on “Module”
7. In the “Module” white screen area, paste the copied code.
8. Close the VBA window. (now the function is created)
9. Use the SpellINR function. (suppose you have number in A1 cell, in B1 cell, use this function =SpellINR(A1). When you press enter, you will get the number converted to words.

Required VBA Code

Copy the VBA code from below.

Function SpellINR(ByVal MyNumber) As String
    Dim WholeNumber As String
    Dim DecimalValue As String
    Dim DecimalWord As String
    Dim Temp As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim DecimalSeparator As String
    Dim UnitName As String
    Dim SubUnitName As String
    Dim DecimalSeparatorIndex As Integer

    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    ' Convert MyNumber to String, preserving decimal separator
    MyNumber = Trim(CStr(MyNumber))
    DecimalSeparatorIndex = InStr(MyNumber, Application.DecimalSeparator)

    If DecimalSeparatorIndex > 0 Then
        DecimalValue = GetTens(Left(Mid(MyNumber, DecimalSeparatorIndex + 1) & "00", 2))
        DecimalWord = " rupees and " & DecimalValue & " paise"
        MyNumber = Trim(Left(MyNumber, DecimalSeparatorIndex - 1))
    End If

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

    If DecimalValue = "" Then
        SpellINR = WholeNumber & " rupees"
    ElseIf DecimalValue = "One" Then
        SpellINR = WholeNumber & " rupees and " & DecimalValue & " paise"
    Else
        SpellINR = WholeNumber & " rupees and " & DecimalValue & " paise"
    End If
End Function

Function GetHundreds(ByVal MyNumber) As String
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)

    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If

    ' Convert the tens and ones place.
    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

Function GetTens(TensText) As String
    Dim Result As String
    Result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        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                                 ' If value between 20-99...
        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))   ' Retrieve ones place.
    End If
    GetTens = Result
End Function

Function GetDigit(Digit) As String
    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

Note: To use the function permanently, you need to save your workbook as “Macro-Enabled Workbook”. For this, click on the file tab/save as/ then choose the macro-enabled type in the file type.