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


Similar Readings


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


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

Read More: How to Convert Number to Words in Excel


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


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

Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

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

    • Reply Avatar photo
      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 Avatar photo
    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 Avatar photo
      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 Avatar photo
      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

       

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo