How to Convert Number to Words in Excel in Rupees

Consider the following simple dataset that contains some numbers which we’ll turn into words in the column next to them.

Sample Data


Step 1 – Using the Developer Tab

  • Select the Developer tab.
  • Click on the Visual Basic command.

Sample Data


Step 2 – Create a New Module to Generate VBA Code

  • The Visual Basic Application window will open.
  • Select the Insert tab.
  • Click on the Module option to create a new Module to write VBA code.

Sample Data

  • 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 the Formulas Tab to Create User Defined Function

  • Select the Formulas tab.
  • Click on the Insert Function command.
  • Select the User-Defined function from the Insert Function category list.
  • 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 the User-Defined Function to Convert Numbers to Words in Excel in Rupees

  • You will get a window of the Word function.
  • Select the first number from column B.
  • Click OK.

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

  • The first number is converted to words in rupees.

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

  • Use the Fill Handle tool and drag it down from cell C5 to C12.

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


Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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

18 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

      • HELLO LUTFOR RAHMAN SHIMANTO.
        COULD YOU PLEASE MAKE THIS FORMULA TO QATAR RIYAL?

        • Lutfor Rahman Shimanto
          Lutfor Rahman Shimanto May 9, 2024 at 11:32 AM

          Dear Abinsh

          Thanks for sharing your requirements. You want to convert numbers to words in Qatar Riyal. Don’t worry! I have modified the previously given code to fulfil your goal.

          Excel VBA User-Defined Function:

          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 Qatar Riyals"
              Else
                  zRStr = " Qatar Riyals " & zRStr
              End If
          
              If zRStr_Paisas <> "" Then
                  zRStr_Paisas = " and " & zRStr_Paisas & " Dirhams"
              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
          

          I hope you have found the solution you were looking for. I have attached the solution workbook. Good luck.

          DOWNLOAD SOLUTION WORKBOOK

          Regards

          Lutfor Rahman Shimanto

          Excel & VBA Developer

          ExcelDemy

  9. When i’m entering more the 100 crores value its showing digits exceeds maximum limit, How to rectify the error.

    • Hello Praveen

      As we used digit limit in our existing code that’s why it is showing this warning.

      Don’t worry! We have updated the existing VBA user-defined function to overcome the problem and work with much larger numbers. In the Indian numbering system, we use terms such as Thousand, Lakh, Crore, Arab, Kharab, Neel, Padma, and Shankh to express large numbers. So, the user-defined function will return the word-converted result using these terms. Moreover, we will use an Excel built-in TEXT function to get accurate results for huge numbers.

      Use the following updated code:

      Function AdvancedWord(SNum As String) 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
      
          zArrPlace = Array("", " Thousand ", " Lakh ", " Crore ", " Arab ", " Kharab ", " Neel ", " Padma ", " Shankh ")
      
          If SNum = "" Then
              AdvancedWord = ""
              Exit Function
          End If
      
          zNumStr = Trim(CStr(SNum))
      
          If zNumStr = "" Then
              AdvancedWord = ""
              Exit Function
          End If
      
          zRStr = ""
      
          If (Val(zNumStr) >= 1E+19) Then
              AdvancedWord = "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
      
          If InStr(zNumStr, "E") > 0 Then
              zNumStr = CDec(zNumStr)
          End If
      
          zP = 0
      
          Do While zNumStr <> ""
              If Len(zNumStr) > 2 Then
                  If zP = 0 Then
                      zTemp = Right(zNumStr, 3)
                      zNumStr = Left(zNumStr, Len(zNumStr) - 3)
                  Else
                      zTemp = Right(zNumStr, 2)
                      zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                  End If
              Else
                  zTemp = zNumStr
                  zNumStr = ""
              End If
      
              zStrTemp = ""
      
              If Val(zTemp) > 99 Then
                  zStrTemp = word_GetH(Right(zTemp, 3), zP)
              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
      
              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, zP As Integer) As String
          
          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
                  zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
              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) 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")
      
          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) 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

      Press Alt plus F11, click on Insert followed by Module, paste the given code in the module and Save

    • Return to the sheet and select the intended cell.
    • Apply the following formula: =AdvancedWord(TEXT(A1,"#.00"))
    • Drag the Fill Handle icon to copy the formula down.
      Choose the intended cell, apply the given formula, drag the Fill Handle icon to copy the formula down
    • Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo