Aung
Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Often, we have to work with the currency of different countries in excel. Therefore, spelling out the currency amount of that specific country in words can sometimes be necessary. This article will show you the step-by-step procedures to Spell a Number in Excel in Rupees.
Download Practice Workbook
Download the following workbook to practice by yourself.
Step by Step Procedures to Spell Number in Rupees in Excel
However, there is no built-in function in excel that can spell numbers in rupees. We have to introduce a function for performing the task. We can easily do that using the Excel VBA. We’ll write a code that will create a new function for our desired task. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains some amounts of Indian Rupees in numbers in column B. And we want to spell the numbers in words in column C. Therefore, go through the below steps carefully to carry out the operation.
STEP 1: Open Excel Workbook
- First of all, we have to open the excel workbook that contains the desired dataset for conversion.
STEP 2: Go to VBA Window
- Then, we have to go to the VBA window.
- For that purpose, click Developer from the ribbon tabs.
- After that, select Visual Basic which is in the Code section.
- Accordingly, the VBA window will emerge.
STEP 3: Insert Module
- Now, we’ll insert a module dialog box where we’ll write the code.
- We can also paste the code if we get it from somewhere else.
- In this regard, click the Insert tab.
- Subsequently, choose Module from the drop-down.
- As a result, the Module window will pop out.
Read More: [Solved] Spell Number Not Working in Excel
STEP 4: Input Code in Module Dialog Box
- Next, copy the following code by pressing the Ctrl and C keys simultaneously.
- Then, go to the Module box.
- Press the Ctrl and V keys at the same time to paste it there.
- See the below picture where we have pasted the code.
Public Function SpellRupee(SNum As String)
Dim xDP As Integer
Dim xArr As Variant
Dim xR_Paisas As String
Dim xNum As String
Dim xF As Integer
Dim xTemp As String
Dim xStr As String
Dim xR As String
Dim xLp As Integer
xArr = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
On Error Resume Next
If SNum = "" Then
SpellRupee = ""
Exit Function
End If
xNum = Trim(Str(SNum))
If xNum = "" Then
SpellRupee = ""
Exit Function
End If
xR = ""
xLp = 0
If (xNum > 999999999.99) Then
SpellRupee = "Exceeds Max limit"
Exit Function
End If
xDP = InStr(xNum, ".")
If xDP > 0 Then
If (Len(xNum) - xDP) = 1 Then
xR_Paisas = SpellRupee_GetT(Left(Mid(xNum, xDP + 1) & "0", 2))
ElseIf (Len(xNum) - xDP) > 1 Then
xR_Paisas = SpellRupee_GetT(Left(Mid(xNum, xDP + 1), 2))
End If
xNum = Trim(Left(xNum, xDP - 1))
End If
xF = 1
Do While xNum <> ""
If (xF >= 2) Then
xTemp = Right(xNum, 2)
Else
If (Len(xNum) = 2) Then
xTemp = Right(xNum, 2)
ElseIf (Len(xNum) = 1) Then
xTemp = Right(xNum, 1)
Else
xTemp = Right(xNum, 3)
End If
End If
xStr = ""
If Val(xTemp) > 99 Then
xStr = SpellRupee_GetH(Right(xTemp, 3), xLp)
If Right(Trim(xStr), 3) <> "Lac" Then
xLp = xLp + 1
End If
ElseIf Val(xTemp) <= 99 And Val(xTemp) > 9 Then
xStr = SpellRupee_GetT(Right(xTemp, 2))
ElseIf Val(xTemp) < 10 Then
xStr = SpellRupee_GetD(Right(xTemp, 2))
End If
If xStr <> "" Then
xR = xStr & xArr(xF) & xR
End If
If xF = 2 Then
If Len(xNum) = 1 Then
xNum = ""
Else
xNum = Left(xNum, Len(xNum) - 2)
End If
ElseIf xF = 3 Then
If Len(xNum) >= 3 Then
xNum = Left(xNum, Len(xNum) - 2)
Else
xNum = ""
End If
ElseIf xF = 4 Then
xNum = ""
Else
If Len(xNum) <= 2 Then
xNum = ""
Else
xNum = Left(xNum, Len(xNum) - 3)
End If
End If
xF = xF + 1
Loop
If xR = "" Then
xR = "No Rupees"
Else
xR = xR & " Rupees "
End If
If xR_Paisas <> "" Then
xR_Paisas = " and " & xR_Paisas & " Paisas"
End If
SpellRupee = xR & xR_Paisas & " Only"
End Function
Function SpellRupee_GetH(xStrH As String, xLp As Integer)
Dim xR As String
If Val(xStrH) < 1 Then
SpellRupee_GetH = ""
Exit Function
Else
xStrH = Right("000" & xStrH, 3)
If Mid(xStrH, 1, 1) <> "0" Then
If (xLp > 0) Then
xR = SpellRupee_GetD(Mid(xStrH, 1, 1)) & " Lac "
Else
xR = SpellRupee_GetD(Mid(xStrH, 1, 1)) & " Hundred "
End If
End If
If Mid(xStrH, 2, 1) <> "0" Then
xR = xR & SpellRupee_GetT(Mid(xStrH, 2))
Else
xR = xR & SpellRupee_GetD(Mid(xStrH, 3))
End If
End If
SpellRupee_GetH = xR
End Function
Function SpellRupee_GetT(xTStr As String)
Dim xTArr1 As Variant
Dim xTArr2 As Variant
Dim xR As String
xTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
xTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Result = ""
If Val(Left(xTStr, 1)) = 1 Then
xR = xTArr1(Val(Mid(xTStr, 2, 1)))
Else
If Val(Left(xTStr, 1)) > 0 Then
xR = xTArr2(Val(Left(xTStr, 1)) - 1)
End If
xR = xR & SpellRupee_GetD(Right(xTStr, 1))
End If
SpellRupee_GetT = xR
End Function
Function SpellRupee_GetD(xDStr As String)
Dim xArr_1() As Variant
xArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
If Val(xDStr) > 0 Then
SpellRupee_GetD = xArr_1(Val(xDStr) - 1)
Else
SpellRupee_GetD = ""
End If
End Function
- Afterward, save the file and close the VBA window.
- In this way, we create the SpellRupee function by applying the code.
Read More: How to Spell Number Without Currency in Excel (2 Suitable Ways)
STEP 5: Apply Function to Spell Number
- Accordingly, go back to the excel worksheet.
- There, select cell C5.
- Then, type the formula:
=SpellRupee(B5)
- Consequently, press Enter.
- Thus, it’ll return the precise output of the number in words.
- Look at the below figure to understand better.
Read More: How to Use Spell Number in Excel (2 Suitable Examples)
Final Output
- Finally, apply the AutoFill tool to get the output of the other amounts.
- Hence, you’ll see the desired results in words.
- You can put any amount in numbers. This function will spell it out in rupees for you.
Conclusion
Henceforth, you will be able to Spell a Number in Excel in Rupees following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
