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
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.
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.
- 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.
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
- How to Spell Number Without Currency in Excel
- How to Spell Number in Taka in ExcelÂ
- How to Spell Number in Dirhams in ExcelÂ
- [Solved] Spell Number Not Working in Excel
- How to Use Spell Number in ExcelÂ
- How to Convert Number to Words in Excel Without VBA
<< Go Back to Spell Number in Excel | Convert Number to Text | Learn Excel
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
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!
Thankyou so much. Since long was trying but finally found the solution
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.
You can use the following code.
Regards
ExcelDemy
Hi ,
How to use this for 2 Decimals
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:
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