How to create a Function in MS EXCEL that can convert figures into words in Indian way.

Sometimes we have to write Amount both in figures and in words in Excel but there is no such inbuilt function in Excel that can help you to convert the figures into words in Indian way. You have to convert the figures manually and then type the words into excel cells. It’s both error prone and time taking.  This post teaches you how to create a user-defined function that will solve the problem.

1. Make your Excel workbook macro enabled .
2. Open the ‘Microsoft Visual Basic’ by pressing ‘Alt+F11’.
3. Go to menu  ‘Insert > Module’. An editing window will open.
4. Simply copy the code (blue coloured text) given below and paste on the ‘Microsoft Visual Basic’ code editor.
5. Save the Module Code in ‘Microsoft Visual Basic’ and close it.
6. Use the user defined function ‘RupeesPaise’ in Excel.

Option Explicit

'This function can convert upto 11 digit & 2 decimal digit into words

'as Rupees and Paise in Indian method . e.g. 99,99,99,99,999.99 will be converted as

'Rupees Ninety Nine Arab Ninety Nine Crore Ninety Nine Lakh Ninety Nine Thousand

'Nine Hundred Ninety Nine Paise Ninety Nine

'Main Function

Function RupeesPaise(ByVal MyNumber)

    Dim Rupees, Paise

    Dim DecimalPlace

    Dim Tens, Hundreds, Thousands, Lakhs, Crores, Arabs

' String representation of amount.

    MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.

    DecimalPlace = InStr(MyNumber, ".")

' Convert Paise and set MyNumber to Rupees amount.

    If DecimalPlace > 0 Then

        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _

                  "00", 2))

        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

    End If

'Sets the length of mynumber 11 digits if it's less

    If Len(MyNumber) = 0 Then

        MyNumber = "00000000000" & MyNumber

    ElseIf Len(MyNumber) = 1 Then

        MyNumber = "0000000000" & MyNumber

    ElseIf Len(MyNumber) = 2 Then

        MyNumber = "000000000" & MyNumber

    ElseIf Len(MyNumber) = 3 Then

        MyNumber = "00000000" & MyNumber

    ElseIf Len(MyNumber) = 4 Then

        MyNumber = "0000000" & MyNumber

    ElseIf Len(MyNumber) = 5 Then

        MyNumber = "000000" & MyNumber

    ElseIf Len(MyNumber) = 6 Then

        MyNumber = "00000" & MyNumber

    ElseIf Len(MyNumber) = 7 Then

        MyNumber = "0000" & MyNumber

    ElseIf Len(MyNumber) = 8 Then

        MyNumber = "000" & MyNumber

    ElseIf Len(MyNumber) = 9 Then

        MyNumber = "00" & MyNumber

    ElseIf Len(MyNumber) = 10 Then

        MyNumber = "0" & MyNumber

    Else

        MyNumber = MyNumber

    End If

' Create string that express Thousand

    Tens = GetTens(Left(Right(MyNumber, 2), 2))

' Create string that express Hundred

    If Left(Right(MyNumber, 3), 1) = "0" Then

        Hundreds = ""

    Else

        Hundreds = GetDigit(Left(Right(MyNumber, 3), 1)) & " Hundred "

    End If

' Create string that espress Thousand

    If Left(Right(MyNumber, 5), 2) = "00" Then

        Thousands = ""

    Else

        Thousands = GetTens(Left(Right(MyNumber, 5), 2)) & " Thousand "

    End If

'Create string that express Lakh

    If Left(Right(MyNumber, 7), 2) = "00" Then

        Lakhs = ""

    Else

        Lakhs = GetTens(Left(Right(MyNumber, 7), 2)) & " Lakh "

    End If

' Create string that espress Crore

    If Left(Right(MyNumber, 9), 2) = "00" Then

        Crores = ""

    Else

        Crores = GetTens(Left(Right(MyNumber, 9), 2)) & " Crore "

    End If

' Create string that express Arab

    If Left(Right(MyNumber, 11), 2) = "00" Then

        Arabs = ""

    Else

        Arabs = GetTens(Left(Right(MyNumber, 11), 2)) & " Arab "

    End If

Rupees = Arabs & Crores & Lakhs & Thousands & Hundreds & Tens

' Insert the word "Rupees"

    If Rupees = "" Then

        Rupees = ""

    ElseIf Rupees = "One" Then

        Rupees = "Rupee" & Rupees

    Else

        Rupees = "Rupees " & Rupees

    End If

' Insert the word "Paise"

    If Paise = "" Then

        Paise = ""

    Else

        Paise = " Paise " & Paise

    End If

RupeesPaise = Rupees & Paise

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

If you face any problem write comment below.