Bishawajit Chakraborty
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.
Step 1: Using the Developer Tab
- First, select the Developer tab.
- Then, click on the Visual Basic command.
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.
- 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
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 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.
- Here, the first number is converted to words 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.
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.
Its showing only formula =word(k35), instead of text.
Pls help.
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.
Wow! Thumbs up for your knowledge and effort for making it easy. Thanks a lot Bro. It worked for me
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.
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
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.
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 ?
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”.
Here, you will see the final result.
I hope this may solve your issue.Â
Bishawajit, on behalf of ExcelDemy
Thankyou so much. Since long was trying but finally found the solution