Article 26 – Converting Amount into Words (Indian Currency – Rupees and Paise)

Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh…..unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System)

Below are numerical equivalent of Indian words

Lakh – 1,00,000
Crore – 1,00,00,000
Arab – 1,00,00,00,000
Kharab – 1,00,00,00,00,000
Neel – 1,00,00,00,00,00,000
Padm – 1,00,00,00,00,00,00,000
Sankh – 1,00,00,00,00,00,00,00,000

The below VBA function was originally written by Yogi Anand and credit is contained in the code itself. I have removed certain bugs and enhanced the code.

1. The original code provided the ability to represent numbers till Arab. Increased the ability to show till Sankh.
2. Increased the ability to handle numbers greater than 15 digits. Numbers greater than 15 digits can be entered as Text.
3. Spacing problems between words have been removed.
4. Corrected following bug – If the value is in 10s (other than 10 itself) in any place, it has unnecessary space character. Like for 4010056 will represented as Rupees Forty  Lakh Ten Thousand Fifty Six Only. Notice there is an additional space between Forty and Lakh. But this behaviour is not exhibited for Ten but will be exhibited for Twenty to Ninety.
5. Removed following bug –  If Decimal has say 45, it correctly shows 45 Paise but if it has say 4, it shows 40  Paise which means that there is one unncessary space character between 40 and Paise.
6. Now an optional parameter can be passed which will show the value upto that. The parameters to be passed are T, L, C, A, K, N, P and S (case insensitive) where T – Thousand, L – Lakh, C – Crore, A – Arab, K – Kharab, N – Neel, P – Padm and S – Sankh. If No parameter or wrong parameter is passed, then it will utilize all. For example, it T is passed for a value 123456 which is One Lakh Twenty Three Thousand and Four Hundred and Fifty Six will be shown as One Twenty Three Thousand Four Hundred Fifty Six. If no parameter is passed, then it will show as One Lakh Twenty Three Thousand and Four Hundred and Fifty Six.

The below code can be copy pasted from below box or can be downloaded from here also Amount into Words VBA Code.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the below code in this

Now, in your worksheet, you can put any of following to display the results
=SpellIndian(A1)
=SpellIndian(A1,"C")
=SpellIndian(20.45)
=SpellIndian("20.45")

Function SpellIndian(ByVal MyNumber, Optional Upto As String)
     '***************************************************************************************************************************
     '**** Yogi Anand -- ANAND Enterprises -- Rochester Hills MI 48309 -- 248-375-5710 www.anandent.com                      ****
     '***************************************************************************************************************************
     '**** 13-Sep-15 Modified by Vijay Verma (https://excelbianalytics.com/wp/) to remove bugs and enhance functionality             ****
     '**** Original (non-modified) code of Yogi Anand can be downloaded from                                                 ****
     '**** https://excelbianalytics.com/wp/wp-content/uploads/2015/09/Yogi_Anand_Spell_Indian_VBA_Code.txt                           ****
     '**** To use this Function, you can call the function as =SpellIndia(A1) or =SpellIndian(39.45)                         ****
     '**** This function can be used for numbers larger than 15 significant digits. Numbers larger than 15 significant       ****
     '**** can be entered as Text
     '**** Now an optional parameter can be passed which will show the value upto that. The parameters to be passed          ****
     '**** are T, L, C, A, K, N, P and S (case insensitive) where T - Thousand, L - Lakh, C - Crore, A - Arab, K - Kharab,   ****
     '**** N - Neel, P - Padm and S - Sankh. If No parameters is passed, then it will utilize all. For example, it T is      ****
     '**** passed for a value 123456 which is One Lakh Twenty Three Thousand and Four Hundred and Fifty Six will be shown as ****
     '**** One Twenty Three Thousand Four Hundred Fifty Six. If no parameter is passed, then it will show as                 ****
     '**** One Lakh Twenty Three Thousand and Four Hundred and Fifty Six                                                     ****
     '****************' Main Function *'*****************************************************************************************
    Dim Rupees, Paise, Temp
    Dim DecimalPlace, Count
    Dim i As Long
    Dim Indian
    Dim Place(99)
    Indian = Array("", "", " Thousand ", " Lakh ", " Crore ", " Arab ", " Kharab ", " Neel ", " Padm ", " Sankh ")
    If UCase(Upto) Like "[!TLCANPS]" Or Upto = "" Then
    For i = 0 To UBound(Indian)
        Place(i) = Indian(i)
    Next i
    Else
    For i = 0 To InStr(1, " TLCANPS", UCase(Upto))
      Place(i) = Indian(i)
    Next i
    End If
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert Paise and set MyNumber to Rupee amount
    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 Temp = GetHundreds(Right(MyNumber, 3))
        If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
        If Place(Count) <> "" Then
            If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
        Else
            If Temp <> "" Then Rupees = Temp & " " & Rupees
        End If
        If Count = 1 And Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            If Count > 1 And Len(MyNumber) > 2 Then
                MyNumber = Left(MyNumber, Len(MyNumber) - 2)
            Else
                MyNumber = ""
            End If
        End If
        Count = Count + 1
     Loop
    Select Case Rupees
    Case ""
        Rupees = "No Rupees"
    Case "One"
        Rupees = "One Rupee"
    Case Else
         '****************************************************************
         'Yogi Anand on 20-Sep-2003
         'modified the following two lines to display "Rupees" to precede
         ' rem'd the first line and added the second line
         '****************************************************************
         'Rupees = Rupees & " Rupees"
        Rupees = "Rupees " & Rupees
        
    End Select
    Select Case Paise
    Case ""
         '****************************************************************
         'Yogi Anand on 20-Sep-2003
         'modified the following two lines to display nothing for no paise
         ' rem'd the first line and added the second line
         '****************************************************************
        
         'Paise = " and No Paise"
         '****************************************************************
         'Yogi Anand on 03-Oct-2003
         'modified the following line to display " Only" for no paise
         ' rem'd the first line and added the second line
         '****************************************************************
         'Paise = ""
        Paise = " Only"
    Case "One"
        Paise = " and One Paisa"
    Case Else
        Paise = " and " & Paise & " Paise"
        
    End Select
    SpellIndian = WorksheetFunction.Trim(Rupees & Paise)
End Function
 '*******************************************
 ' Converts a number from 100-999 into text *
 '*******************************************
Function GetHundreds(ByVal MyNumber)
    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
 '*********************************************
 ' Converts a number from 10 to 99 into text. *
 '*********************************************
Function GetTens(TensText)
    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
 '*******************************************
 ' Converts a number from 1 to 9 into text. *
 '*******************************************
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