How to Convert Number to Words in Excel in Rupees

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a fantastic tool for performing various calculations. It was first created to handle big data arrays. However, it also enables the quick and efficient creation of accounting documents like invoices, assessments, or balance sheets. In this article, we will show you how to convert Number to Words in Excel in Rupees.


How to Convert Number to Words in Excel in Rupees: Step-by-Step Procedures

It’s quite easy to construct a macro function in Microsoft Excel for converting numbers into words in rupees. Besides, you must utilize macros to convert a number into words in Microsoft Excel. You will have a permanent formula or function for converting numbers to words in Indian rupees. You may use it as a function to transform any column of data by just dragging and dropping the formula. Here, we will generate a VBA code and apply a User Defined function to convert Numbers to Words in Excel in Rupees. Firstly, Let’s suppose, we have a sample data set to convert Number to Words in Excel.

Sample Data


Step 1: Using the Developer Tab

  • First, select the Developer tab.
  • Then, click on the Visual Basic command.

Sample Data


Step 2: Create New Module to Generate VBA Code

  • Now, the Visual Basic Application window will open.
  • Next, select the Insert tab.
  • Finally, click on the Module option to create a new Module to write a VBA code.

Sample Data

  • Now, paste the following VBA code into the Module.
Function word(SNum As String)
'Declare the Variables
Dim zDPInt As Integer
Dim zArrPlace As Variant
Dim zRStr_Paisas As String
Dim zNumStr As String
Dim zP As Integer
Dim zTemp As String
Dim zStrTemp As String
Dim zRStr As String
Dim zBp As Integer
zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
On Error Resume Next
If SNum = "" Then
  word = ""
  Exit Function
End If
zNumStr = Trim(Str(SNum))
If zNumStr = "" Then
  word = ""
  Exit Function
End If
 
zRStr = ""
zBp = 0
If (zNumStr > 999999999.99) Then
    word = "Digit excced Maximum limit"
    Exit Function
End If
zDPInt = InStr(zNumStr, ".")
If zDPInt > 0 Then
    If (Len(zNumStr) - zDPInt) = 1 Then
       zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
    ElseIf (Len(xNumStr) - xDPInt) > 1 Then
       zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
    End If
        zNumStr = Trim(Left(zNumStr, zDPInt - 1))
    End If
    zP = 1
    Do While zNumStr <> ""
        If (zP >= 2) Then
            zTemp = Right(zNumStr, 2)
        Else
            If (Len(zNumStr) = 2) Then
                zTemp = Right(zNumStr, 2)
            ElseIf (Len(zNumStr) = 1) Then
                zTemp = Right(zNumStr, 1)
            Else
                zTemp = Right(zNumStr, 3)
            End If
        End If
        zStrTemp = ""
        If Val(zTemp) > 99 Then
            zStrTemp = word_GetH(Right(zTemp, 3), zBp)
            If Right(Trim(xStrTemp), 3) <> "Lac" Then
            zBp = zBp + 1
            End If
        ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
            zStrTemp = word_GetT(Right(zTemp, 2))
        ElseIf Val(zTemp) < 10 Then
            zStrTemp = word_GetD(Right(zTemp, 2))
        End If
        If zStrTemp <> "" Then
            zRStr = zStrTemp & zArrPlace(zP) & zRStr
        End If
        If zP = 2 Then
            If Len(zNumStr) = 1 Then
                zNumStr = ""
            Else
                zNumStr = Left(zNumStr, Len(zNumStr) - 2)
            End If
       ElseIf zP = 3 Then
            If Len(zNumStr) >= 3 Then
                 zNumStr = Left(zNumStr, Len(zNumStr) - 2)
            Else
                zNumStr = ""
            End If
        ElseIf zP = 4 Then
          zNumStr = ""
    Else
        If Len(zNumStr) <= 2 Then
        zNumStr = ""
    Else
        zNumStr = Left(zNumStr, Len(zNumStr) - 3)
        End If
    End If
        zP = zP + 1
Loop
    If zRStr = "" Then
       zRStr = "No Rupees"
    Else
       zRStr = " Rupees " & zRStr
    End If
    If zRStr_Paisas <> "" Then
       zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
    End If
    word = zRStr & zRStr_Paisas & " Only"
    End Function
Function word_GetH(zStrH As String, zBp As Integer)
Dim zRStr As String
If Val(zStrH) < 1 Then
    word_GetH = ""
    Exit Function
    'Converts a Number from 100 to 999 into Word
Else
   zStrH = Right("000" & zStrH, 3)
   If Mid(zStrH, 1, 1) <> "0" Then
        If (zBp > 0) Then
         zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
        Else
         zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
        End If
    End If
    If Mid(zStrH, 2, 1) <> "0" Then
        zRStr = zRStr & word_GetT(Mid(zStrH, 2))
    Else
        zRStr = zRStr & word_GetD(Mid(zStrH, 3))
    End If
End If
    word_GetH = zRStr
End Function
Function word_GetT(zTStr As String)
    Dim zTArr1 As Variant
    Dim zTArr2 As Variant
    Dim zRStr As String
    'Converts a Number from 10 to 19 into Word
    
    zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    'Converts a Number from 20 to 99 into Word
    
    zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
    Result = ""
    If Val(Left(zTStr, 1)) = 1 Then
        zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
    Else
        If Val(Left(zTStr, 1)) > 0 Then
            zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
        End If
        zRStr = zRStr & word_GetD(Right(zTStr, 1))
    End If
      word_GetT = zRStr
End Function
Function word_GetD(zDStr As String)
Dim zArr_1() As Variant
'Converts a Number from 1 to 9 into Word

    zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
    If Val(zDStr) > 0 Then
        word_GetD = zArr_1(Val(zDStr) - 1)
    Else
        word_GetD = ""
    End If
End Function

Step-by-Step Procedures to Convert Number to Words in Excel in Rupees


Step 3: Using Formulas Tab to Create User Defined Function

  • First, select the Formulas tab.
  • Secondly, click on the Insert Function command.
  • Now, select the User Defined function from the Insert Function category list.
  • Lastly, click OK.

Step-by-Step Procedures to Convert Number to Words in Excel in Rupees

Read More: How to Convert Peso Number to Words in Excel


Step 4: Applying User Defined Function to Convert Number to Words in Excel in Rupees

  • Lastly, after completing Step 3, you will see this window of the Word function.
  • Here, select the first number from column B.
  • Therefore, click OK.

Step-by-Step Procedures to Convert Number to Words in Excel in Rupees

  • Here, the first number is converted to words in rupees.

Step-by-Step Procedures to Convert Number to Words in Excel in Rupees

  • Finally, use the Fill Handle tool and drag it down from cell C5 to C12. Therefore, we get the results of other cells.

Step-by-Step Procedures to Convert Number to Words in Excel in Rupees


Download Practice Workbook


Conclusion

In this article, I’ve covered step by step process of how to Convert Number to Words in Excel in Rupees. I sincerely hope you enjoyed and learned a lot from this article.

If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles


<< Go Back to Spell Number in Excel | Convert Number to Text | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

14 Comments
  1. Its showing only formula =word(k35), instead of text.
    Pls help.

    • Reply Bishawajit Chakraborty
      Bishawajit Chakraborty Oct 19, 2022 at 3:13 PM

      Thank you, Dhroov, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.

  2. Wow! Thumbs up for your knowledge and effort for making it easy. Thanks a lot Bro. It worked for me

  3. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Nov 16, 2022 at 11:28 AM

    Thank you so much Sharath for your response. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

    Regards,

    Bishawajit Chakraborty.

  4. when i close file and next time it open that function not work ……all process repeat starting …..we type word(F28 ) that not work and written there Name like this

    • Reply Bishawajit Chakraborty
      Bishawajit Chakraborty Nov 23, 2022 at 9:57 AM

      Thank you, Madan, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.

  5. Thanks very much for this.
    I have been able to work it out with small edits to suit my country’s currency.
    How do I insert the word “and” in the formulae to read for example,
    Instead of writing: “Rupees Six Hundred Forty Five only” or “Rupees Six thousand four hundred forty five thousand only”, etc, it will now read “Rupees Six hundred and forty five only” or “Rupees Six thousand four hundred and forty five thousand only”, etc ?

    • Reply Bishawajit Chakraborty
      Bishawajit Chakraborty Feb 19, 2023 at 11:28 AM

      Thank you, MEREDITH, for your wonderful question. 

      Here is our modified VBA code. Therefore, you can apply this code to solve your problem but you can also modified this code for three digit numbers by using word “and” in our code section.

      Here is the image of modified code where we have added the word “and”.

      Function word(SNum As String)
      'Declare the Variables
      Dim zDPInt As Integer
      Dim zArrPlace As Variant
      Dim zRStr_Paisas As String
      Dim zNumStr As String
      Dim zP As Integer
      Dim zTemp As String
      Dim zStrTemp As String
      Dim zRStr As String
      Dim zBp As Integer
      zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
      On Error Resume Next
      If SNum = "" Then
      word = ""
      Exit Function
      End If
      zNumStr = Trim(Str(SNum))
      If zNumStr = "" Then
      word = ""
      Exit Function
      End If
      
      zRStr = ""
      zBp = 0
      If (zNumStr > 999999999.99) Then
      word = "Digit excced Maximum limit"
      Exit Function
      End If
      zDPInt = InStr(zNumStr, ".")
      If zDPInt > 0 Then
      If (Len(zNumStr) - zDPInt) = 1 Then
      zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
      ElseIf (Len(xNumStr) - xDPInt) > 1 Then
      zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
      End If
      zNumStr = Trim(Left(zNumStr, zDPInt - 1))
      End If
      zP = 1
      Do While zNumStr <> ""
      If (zP >= 2) Then
      zTemp = Right(zNumStr, 2)
      Else
      If (Len(zNumStr) = 2) Then
      zTemp = Right(zNumStr, 2)
      ElseIf (Len(zNumStr) = 1) Then
      zTemp = Right(zNumStr, 1)
      Else
      zTemp = Right(zNumStr, 3)
      End If
      End If
      zStrTemp = ""
      If Val(zTemp) > 99 Then
      zStrTemp = word_GetH(Right(zTemp, 3), zBp)
      If Right(Trim(xStrTemp), 3) <> "Lac" Then
      zBp = zBp + 1
      End If
      ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
      zStrTemp = word_GetT(Right(zTemp, 2))
      ElseIf Val(zTemp) < 10 Then
      zStrTemp = word_GetD(Right(zTemp, 2))
      End If
      If zStrTemp <> "" Then
      zRStr = zStrTemp & zArrPlace(zP) & zRStr
      End If
      If zP = 2 Then
      If Len(zNumStr) = 1 Then
      zNumStr = ""
      Else
      zNumStr = Left(zNumStr, Len(zNumStr) - 2)
      End If
      ElseIf zP = 3 Then
      If Len(zNumStr) >= 3 Then
      zNumStr = Left(zNumStr, Len(zNumStr) - 2)
      Else
      zNumStr = ""
      End If
      ElseIf zP = 4 Then
      zNumStr = ""
      Else
      If Len(zNumStr) <= 2 Then
      zNumStr = ""
      Else
      zNumStr = Left(zNumStr, Len(zNumStr) - 3)
      End If
      End If
      zP = zP + 1
      Loop
      If zRStr = "" Then
      zRStr = "No Rupees"
      Else
      zRStr = " Rupees " & zRStr
      End If
      If zRStr_Paisas <> "" Then
      zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
      End If
      word = zRStr & zRStr_Paisas & " Only"
      End Function
      Function word_GetH(zStrH As String, zBp As Integer)
      Dim zRStr As String
      If Val(zStrH) < 1 Then
      word_GetH = ""
      Exit Function
      'Converts a Number from 100 to 999 into Word
      Else
      zStrH = Right("000" & zStrH, 3)
      If Mid(zStrH, 1, 1) <> "0" Then
      If (zBp > 0) Then
      zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
      Else
      zRStr = " and " & word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
      End If
      End If
      If Mid(zStrH, 2, 1) <> "0" Then
      zRStr = zRStr & word_GetT(Mid(zStrH, 2))
      Else
      zRStr = zRStr & word_GetD(Mid(zStrH, 3))
      End If
      End If
      word_GetH = zRStr
      End Function
      Function word_GetT(zTStr As String)
      Dim zTArr1 As Variant
      Dim zTArr2 As Variant
      Dim zRStr As String
      'Converts a Number from 10 to 19 into Word
      
      zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
      'Converts a Number from 20 to 99 into Word
      
      zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
      Result = ""
      If Val(Left(zTStr, 1)) = 1 Then
      zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
      Else
      If Val(Left(zTStr, 1)) > 0 Then
      zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
      End If
      zRStr = zRStr & word_GetD(Right(zTStr, 1))
      End If
      word_GetT = zRStr
      End Function
      Function word_GetD(zDStr As String)
      Dim zArr_1() As Variant
      'Converts a Number from 1 to 9 into Word
      
      zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
      If Val(zDStr) > 0 Then
      word_GetD = zArr_1(Val(zDStr) - 1)
      Else
      word_GetD = ""
      End If
      End Function

      Here, you will see the final result.

      I hope this may solve your issue. 

      Bishawajit, on behalf of ExcelDemy

       

      • Thank you for this wonderful code. It is really nice and useful. However, there is a small glitch – “and” is in wrong place. For example, “22,44,556” expresses as “Rupees Twenty Two Lacs Forty Four Thousand and Five Hundred Fifty Six Only”, but it should be “Rupees Twenty Two Lacs Forty Four Thousand Five Hundred and Fifty Six Only”. Also, there is an extra blank on each side of “and”. Can you please rectify this minor issue? Otherwise your code is excellent!

  6. Thankyou so much. Since long was trying but finally found the solution

  7. Reply
    Syed Mohisn Ahmed Feb 14, 2024 at 1:00 PM

    I want to use this formula for preparing checks , and in our banking systems we can use the word “rupees” in starting , can you please modified the coding and reply me as soon as possible

    E.g : One Hundred Seventy Five Thousand Only which is (175,000/-) in numbers.

    Please response as soon as possible

    • Hello Syed Mohisn Ahmed,

      Updated the code in the marked section. Where I replaced “Rupees” text from condition.

      Updated Code

      You can use the following code.

      Function word(SNum As String)
      'Declare the Variables
      Dim zDPInt As Integer
      Dim zArrPlace As Variant
      Dim zRStr_Paisas As String
      Dim zNumStr As String
      Dim zP As Integer
      Dim zTemp As String
      Dim zStrTemp As String
      Dim zRStr As String
      Dim zBp As Integer
      zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
      On Error Resume Next
      If SNum = "" Then
        word = ""
        Exit Function
      End If
      zNumStr = Trim(Str(SNum))
      If zNumStr = "" Then
        word = ""
        Exit Function
      End If
       
      zRStr = ""
      zBp = 0
      If (zNumStr > 999999999.99) Then
          word = "Digit excced Maximum limit"
          Exit Function
      End If
      zDPInt = InStr(zNumStr, ".")
      If zDPInt > 0 Then
          If (Len(zNumStr) - zDPInt) = 1 Then
             zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
          ElseIf (Len(xNumStr) - xDPInt) > 1 Then
             zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
          End If
              zNumStr = Trim(Left(zNumStr, zDPInt - 1))
          End If
          zP = 1
          Do While zNumStr <> ""
              If (zP >= 2) Then
                  zTemp = Right(zNumStr, 2)
              Else
                  If (Len(zNumStr) = 2) Then
                      zTemp = Right(zNumStr, 2)
                  ElseIf (Len(zNumStr) = 1) Then
                      zTemp = Right(zNumStr, 1)
                  Else
                      zTemp = Right(zNumStr, 3)
                  End If
              End If
              zStrTemp = ""
              If Val(zTemp) > 99 Then
                  zStrTemp = word_GetH(Right(zTemp, 3), zBp)
                  If Right(Trim(xStrTemp), 3) <> "Lac" Then
                  zBp = zBp + 1
                  End If
              ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
                  zStrTemp = word_GetT(Right(zTemp, 2))
              ElseIf Val(zTemp) < 10 Then
                  zStrTemp = word_GetD(Right(zTemp, 2))
              End If
              If zStrTemp <> "" Then
                  zRStr = zStrTemp & zArrPlace(zP) & zRStr
              End If
              If zP = 2 Then
                  If Len(zNumStr) = 1 Then
                      zNumStr = ""
                  Else
                      zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                  End If
             ElseIf zP = 3 Then
                  If Len(zNumStr) >= 3 Then
                       zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                  Else
                      zNumStr = ""
                  End If
              ElseIf zP = 4 Then
                zNumStr = ""
          Else
              If Len(zNumStr) <= 2 Then
              zNumStr = ""
          Else
              zNumStr = Left(zNumStr, Len(zNumStr) - 3)
              End If
          End If
              zP = zP + 1
      Loop
          If zRStr = "" Then
             zRStr = "No Rupees"
          Else
             zRStr = " Rupees " & zRStr
          End If
          If zRStr_Paisas <> "" Then
             zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
          End If
          word = zRStr & zRStr_Paisas & " Only"
          End Function
      Function word_GetH(zStrH As String, zBp As Integer)
      Dim zRStr As String
      If Val(zStrH) < 1 Then
          word_GetH = ""
          Exit Function
          'Converts a Number from 100 to 999 into Word
      Else
         zStrH = Right("000" & zStrH, 3)
         If Mid(zStrH, 1, 1) <> "0" Then
              If (zBp > 0) Then
               zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
              Else
               zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
              End If
          End If
          If Mid(zStrH, 2, 1) <> "0" Then
              zRStr = zRStr & word_GetT(Mid(zStrH, 2))
          Else
              zRStr = zRStr & word_GetD(Mid(zStrH, 3))
          End If
      End If
          word_GetH = zRStr
      End Function
      Function word_GetT(zTStr As String)
          Dim zTArr1 As Variant
          Dim zTArr2 As Variant
          Dim zRStr As String
          'Converts a Number from 10 to 19 into Word
          
          zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
          'Converts a Number from 20 to 99 into Word
          
          zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
          Result = ""
          If Val(Left(zTStr, 1)) = 1 Then
              zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
          Else
              If Val(Left(zTStr, 1)) > 0 Then
                  zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
              End If
              zRStr = zRStr & word_GetD(Right(zTStr, 1))
          End If
            word_GetT = zRStr
      End Function
      Function word_GetD(zDStr As String)
      Dim zArr_1() As Variant
      'Converts a Number from 1 to 9 into Word
      
          zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
          If Val(zDStr) > 0 Then
              word_GetD = zArr_1(Val(zDStr) - 1)
          Else
              word_GetD = ""
          End If
      End Function
      

      Regards
      ExcelDemy

  8. Hi ,

    How to use this for 2 Decimals

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 14, 2024 at 4:33 PM

      Hello CHANDRA

      Thanks for visiting our blog and posting an exciting comment. You want to convert decimal numbers (up to two decimal places) to words in Rupees.

      To do so, follow these:

      1. Right-click on the sheet name tab.
      2. Click on View Code.
      3. Now, please insert the following code in the sheet module and save it:
        Function AdvancedWord(SNum As String)
        
            Dim zDPInt As Integer
            Dim zArrPlace As Variant
            Dim zRStr_Paisas As String
            Dim zNumStr As String
            Dim zP As Integer
            Dim zTemp As String
            Dim zStrTemp As String
            Dim zRStr As String
            Dim zBp As Integer
            
            zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
            
            On Error Resume Next
            
            If SNum = "" Then
                word = ""
                Exit Function
            End If
            
            zNumStr = Trim(Str(SNum))
            
            If zNumStr = "" Then
                word = ""
                Exit Function
            End If
            
            zRStr = ""
            zBp = 0
            
            If (zNumStr > 999999999.99) Then
                word = "Digit exceeds Maximum limit"
                Exit Function
            End If
            
            zDPInt = InStr(zNumStr, ".")
            
            If zDPInt > 0 Then
                If (Len(zNumStr) - zDPInt) = 1 Then
                    zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
                ElseIf (Len(zNumStr) - zDPInt) > 1 Then
                    zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
                End If
                zNumStr = Trim(Left(zNumStr, zDPInt - 1))
            End If
            
            zP = 1
            
            Do While zNumStr <> ""
                If (zP >= 2) Then
                    zTemp = Right(zNumStr, 2)
                Else
                    If (Len(zNumStr) = 2) Then
                        zTemp = Right(zNumStr, 2)
                    ElseIf (Len(zNumStr) = 1) Then
                        zTemp = Right(zNumStr, 1)
                    Else
                        zTemp = Right(zNumStr, 3)
                    End If
                End If
                
                zStrTemp = ""
                
                If Val(zTemp) > 99 Then
                    zStrTemp = word_GetH(Right(zTemp, 3), zBp)
                    If Right(Trim(zStrTemp), 3) <> "Lac" Then
                        zBp = zBp + 1
                    End If
                ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
                    zStrTemp = word_GetT(Right(zTemp, 2))
                ElseIf Val(zTemp) < 10 Then
                    zStrTemp = word_GetD(Right(zTemp, 2))
                End If
                
                If zStrTemp <> "" Then
                    zRStr = zStrTemp & zArrPlace(zP) & zRStr
                End If
                
                If zP = 2 Then
                    If Len(zNumStr) = 1 Then
                        zNumStr = ""
                    Else
                        zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                    End If
                ElseIf zP = 3 Then
                    If Len(zNumStr) >= 3 Then
                        zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                    Else
                        zNumStr = ""
                    End If
                ElseIf zP = 4 Then
                    zNumStr = ""
                Else
                    If Len(zNumStr) <= 2 Then
                        zNumStr = ""
                    Else
                        zNumStr = Left(zNumStr, Len(zNumStr) - 3)
                    End If
                End If
                
                zP = zP + 1
            Loop
            
            If zRStr = "" Then
                zRStr = "No Rupees"
            Else
                zRStr = " Rupees " & zRStr
            End If
            
            If zRStr_Paisas <> "" Then
                zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
            End If
            
            AdvancedWord = zRStr & zRStr_Paisas & " Only"
        End Function
        
        Function word_GetH(zStrH As String, zBp As Integer)
            Dim zRStr As String
            
            If Val(zStrH) < 1 Then
                word_GetH = ""
                Exit Function
            Else
                zStrH = Right("000" & zStrH, 3)
                If Mid(zStrH, 1, 1) <> "0" Then
                    If (zBp > 0) Then
                        zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
                    Else
                        zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
                    End If
                End If
                
                If Mid(zStrH, 2, 1) <> "0" Then
                    zRStr = zRStr & word_GetT(Mid(zStrH, 2))
                Else
                    zRStr = zRStr & word_GetD(Mid(zStrH, 3))
                End If
            End If
            
            word_GetH = zRStr
        End Function
        
        Function word_GetT(zTStr As String)
            Dim zTArr1 As Variant
            Dim zTArr2 As Variant
            Dim zRStr As String
            
            zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
            zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
            Result = ""
            
            If Val(Left(zTStr, 1)) = 1 Then
                zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
            Else
                If Val(Left(zTStr, 1)) > 0 Then
                    zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
                End If
                zRStr = zRStr & word_GetD(Right(zTStr, 1))
            End If
            
            word_GetT = zRStr
        End Function
        
        Function word_GetD(zDStr As String)
            Dim zArr_1() As Variant
            
            zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
            
            If Val(zDStr) > 0 Then
                word_GetD = zArr_1(Val(zDStr) - 1)
            Else
                word_GetD = ""
            End If
        End Function

      4. Return to the sheet and select the intended cell.
      5. Apply the following formula: =AdvancedWord(B5)
      6. Drag the Fill Handle icon to copy the formula down.

      I am also attaching the solution workbook for better understanding. I hope the solution will help you; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards

      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo