## How to convert numbers to words in Indian rupees in Excel?

This article, I will introduce how to convert a list of numbers to words in Indian rupees or English dollar in Excel. Convert numbers to words in Indian rupees with VBA code

Convert numbers to words in English dollar with an amazing feature

#### Convert numbers to words in Indian rupees with VBA code

The following VBA code can help you to convert the numbers to words in rupees, please do as this:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Convert numbers to words in rupees

``````Public Function RupeeFormat(SNum As String)
'Updateby Extendoffice
Dim xDPInt As Integer
Dim xArrPlace As Variant
Dim xRStr_Paisas As String
Dim xNumStr As String
Dim xF As Integer
Dim xTemp As String
Dim xStrTemp As String
Dim xRStr As String
Dim xLp As Integer
xArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
On Error Resume Next
If SNum = "" Then
RupeeFormat = ""
Exit Function
End If
xNumStr = Trim(str(SNum))
If xNumStr = "" Then
RupeeFormat = ""
Exit Function
End If

xRStr = ""
xLp = 0
If (xNumStr > 999999999.99) Then
RupeeFormat = "Digit excced Maximum limit"
Exit Function
End If
xDPInt = InStr(xNumStr, ".")
If xDPInt > 0 Then
If (Len(xNumStr) - xDPInt) = 1 Then
xRStr_Paisas = RupeeFormat_GetT(Left(Mid(xNumStr, xDPInt + 1) & "0", 2))
ElseIf (Len(xNumStr) - xDPInt) > 1 Then
xRStr_Paisas = RupeeFormat_GetT(Left(Mid(xNumStr, xDPInt + 1), 2))
End If
xNumStr = Trim(Left(xNumStr, xDPInt - 1))
End If
xF = 1
Do While xNumStr <> ""
If (xF >= 2) Then
xTemp = Right(xNumStr, 2)
Else
If (Len(xNumStr) = 2) Then
xTemp = Right(xNumStr, 2)
ElseIf (Len(xNumStr) = 1) Then
xTemp = Right(xNumStr, 1)
Else
xTemp = Right(xNumStr, 3)
End If
End If
xStrTemp = ""
If Val(xTemp) > 99 Then
xStrTemp = RupeeFormat_GetH(Right(xTemp, 3), xLp)
If Right(Trim(xStrTemp), 3) <> "Lac" Then
xLp = xLp + 1
End If
ElseIf Val(xTemp) <= 99 And Val(xTemp) > 9 Then
xStrTemp = RupeeFormat_GetT(Right(xTemp, 2))
ElseIf Val(xTemp) < 10 Then
xStrTemp = RupeeFormat_GetD(Right(xTemp, 2))
End If
If xStrTemp <> "" Then
xRStr = xStrTemp & xArrPlace(xF) & xRStr
End If
If xF = 2 Then
If Len(xNumStr) = 1 Then
xNumStr = ""
Else
xNumStr = Left(xNumStr, Len(xNumStr) - 2)
End If
ElseIf xF = 3 Then
If Len(xNumStr) >= 3 Then
xNumStr = Left(xNumStr, Len(xNumStr) - 2)
Else
xNumStr = ""
End If
ElseIf xF = 4 Then
xNumStr = ""
Else
If Len(xNumStr) <= 2 Then
xNumStr = ""
Else
xNumStr = Left(xNumStr, Len(xNumStr) - 3)
End If
End If
xF = xF + 1
Loop
If xRStr = "" Then
xRStr = "No Rupees"
Else
xRStr = " Rupees " & xRStr
End If
If xRStr_Paisas <> "" Then
xRStr_Paisas = " and " & xRStr_Paisas & " Paisas"
End If
RupeeFormat = xRStr & xRStr_Paisas & " Only"
End Function
Function RupeeFormat_GetH(xStrH As String, xLp As Integer)
Dim xRStr As String
If Val(xStrH) < 1 Then
RupeeFormat_GetH = ""
Exit Function
Else
xStrH = Right("000" & xStrH, 3)
If Mid(xStrH, 1, 1) <> "0" Then
If (xLp > 0) Then
xRStr = RupeeFormat_GetD(Mid(xStrH, 1, 1)) & " Lac "
Else
xRStr = RupeeFormat_GetD(Mid(xStrH, 1, 1)) & " Hundred "
End If
End If
If Mid(xStrH, 2, 1) <> "0" Then
xRStr = xRStr & RupeeFormat_GetT(Mid(xStrH, 2))
Else
xRStr = xRStr & RupeeFormat_GetD(Mid(xStrH, 3))
End If
End If
RupeeFormat_GetH = xRStr
End Function
Function RupeeFormat_GetT(xTStr As String)
Dim xTArr1 As Variant
Dim xTArr2 As Variant
Dim xRStr 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
xRStr = xTArr1(Val(Mid(xTStr, 2, 1)))
Else
If Val(Left(xTStr, 1)) > 0 Then
xRStr = xTArr2(Val(Left(xTStr, 1)) - 1)
End If
xRStr = xRStr & RupeeFormat_GetD(Right(xTStr, 1))
End If
RupeeFormat_GetT = xRStr
End Function
Function RupeeFormat_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
RupeeFormat_GetD = xArr_1(Val(xDStr) - 1)
Else
RupeeFormat_GetD = ""
End If
End Function
``````

3. After inserting the code, save and close the code window, go back to the worksheet, and enter this formula: =RupeeFormat(A2) into a blank cell, and then drag the fill handle down to apply this formula to other cells, all the numbers have been spelled out in rupees, see screenshot: #### Convert numbers to words in English dollar with an amazing feature

If you want to convert the numbers to words in English dollar, Kutools for Excel’s Numbers to Words feature can help you to solve this job quickly and easily.

Tips:To apply this Number to Words feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Select the list of numbers you want to convert, and then click Kutools > Content > Numbers to Words, see screenshots: 2. In the Numbers to Currency Words dialog box, select English option from the Languages section, and then click Ok button, the numbers in the selection have been converted to the English currency words, see screenshot:   ## The Best Office Productivity Tools

### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name... ### Office Tab - brings tabbed interface to Office, and make your work much easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
The VBA code works effectively. Thanks for sharing these simple and easy to follow tips & steps.
This comment was minimized by the moderator on the site
Can u pls share the code that would spell number for 9000 crore
This comment was minimized by the moderator on the site
Hey, Great Help! Thanks for creating such a wonderful code.

It has saved a lot of time. "God Bless You!!"
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Hi Sir, Is it possible to set this for by default for every excel working sheet or not ?
This comment was minimized by the moderator on the site
Hello, Mukesh

Yes, as long as the code is copied into the vba window module, the formula can be applied to the whole workbook.
But when closing the workbook, you should save it as Excel Macro-Enabled Workbook file format.
Please have a try, thank you!
This comment was minimized by the moderator on the site
Thanks a lot it is very Helpful
This comment was minimized by the moderator on the site
But this applicable to only one excel. If I enter this formula in other excel this is not working
what is solution for this
This comment was minimized by the moderator on the site
Hello,

If you have several workbooks, you need to copy the code to several workbooks.
If you only copy it to one workbook, it will not work in other workbooks. 😀
This comment was minimized by the moderator on the site
How can remove the word "Rupees"? Normally cheque leaves have the word "Rupees" already.
This comment was minimized by the moderator on the site
Hello, Sakariya
``````Public Function RupeeFormat(SNum As String)
'Updateby Extendoffice
Dim xDPInt As Integer
Dim xArrPlace As Variant
Dim xRStr_Paisas As String
Dim xNumStr As String
Dim xF As Integer
Dim xTemp As String
Dim xStrTemp As String
Dim xRStr As String
Dim xLp As Integer
xArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
On Error Resume Next
If SNum = "" Then
RupeeFormat = ""
Exit Function
End If
xNumStr = Trim(Str(SNum))
If xNumStr = "" Then
RupeeFormat = ""
Exit Function
End If

xRStr = ""
xLp = 0
If (xNumStr > 999999999.99) Then
RupeeFormat = "Digit excced Maximum limit"
Exit Function
End If
xDPInt = InStr(xNumStr, ".")
If xDPInt > 0 Then
If (Len(xNumStr) - xDPInt) = 1 Then
xRStr_Paisas = RupeeFormat_GetT(Left(Mid(xNumStr, xDPInt + 1) & "0", 2))
ElseIf (Len(xNumStr) - xDPInt) > 1 Then
xRStr_Paisas = RupeeFormat_GetT(Left(Mid(xNumStr, xDPInt + 1), 2))
End If
xNumStr = Trim(Left(xNumStr, xDPInt - 1))
End If
xF = 1
Do While xNumStr <> ""
If (xF >= 2) Then
xTemp = Right(xNumStr, 2)
Else
If (Len(xNumStr) = 2) Then
xTemp = Right(xNumStr, 2)
ElseIf (Len(xNumStr) = 1) Then
xTemp = Right(xNumStr, 1)
Else
xTemp = Right(xNumStr, 3)
End If
End If
xStrTemp = ""
If Val(xTemp) > 99 Then
xStrTemp = RupeeFormat_GetH(Right(xTemp, 3), xLp)
If Right(Trim(xStrTemp), 3) <> "Lac" Then
xLp = xLp + 1
End If
ElseIf Val(xTemp) <= 99 And Val(xTemp) > 9 Then
xStrTemp = RupeeFormat_GetT(Right(xTemp, 2))
ElseIf Val(xTemp) < 10 Then
xStrTemp = RupeeFormat_GetD(Right(xTemp, 2))
End If
If xStrTemp <> "" Then
xRStr = xStrTemp & xArrPlace(xF) & xRStr
End If
If xF = 2 Then
If Len(xNumStr) = 1 Then
xNumStr = ""
Else
xNumStr = Left(xNumStr, Len(xNumStr) - 2)
End If
ElseIf xF = 3 Then
If Len(xNumStr) >= 3 Then
xNumStr = Left(xNumStr, Len(xNumStr) - 2)
Else
xNumStr = ""
End If
ElseIf xF = 4 Then
xNumStr = ""
Else
If Len(xNumStr) <= 2 Then
xNumStr = ""
Else
xNumStr = Left(xNumStr, Len(xNumStr) - 3)
End If
End If
xF = xF + 1
Loop
If xRStr = "" Then
xRStr = "No Rupees"
Else
xRStr = xRStr
End If
If xRStr_Paisas <> "" Then
xRStr_Paisas = " and " & xRStr_Paisas & " Paisas"
End If
RupeeFormat = xRStr & xRStr_Paisas & " Only"
End Function
Function RupeeFormat_GetH(xStrH As String, xLp As Integer)
Dim xRStr As String
If Val(xStrH) < 1 Then
RupeeFormat_GetH = ""
Exit Function
Else
xStrH = Right("000" & xStrH, 3)
If Mid(xStrH, 1, 1) <> "0" Then
If (xLp > 0) Then
xRStr = RupeeFormat_GetD(Mid(xStrH, 1, 1)) & " Lac "
Else
xRStr = RupeeFormat_GetD(Mid(xStrH, 1, 1)) & " Hundred "
End If
End If
If Mid(xStrH, 2, 1) <> "0" Then
xRStr = xRStr & RupeeFormat_GetT(Mid(xStrH, 2))
Else
xRStr = xRStr & RupeeFormat_GetD(Mid(xStrH, 3))
End If
End If
RupeeFormat_GetH = xRStr
End Function
Function RupeeFormat_GetT(xTStr As String)
Dim xTArr1 As Variant
Dim xTArr2 As Variant
Dim xRStr 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
xRStr = xTArr1(Val(Mid(xTStr, 2, 1)))
Else
If Val(Left(xTStr, 1)) > 0 Then
xRStr = xTArr2(Val(Left(xTStr, 1)) - 1)
End If
xRStr = xRStr & RupeeFormat_GetD(Right(xTStr, 1))
End If
RupeeFormat_GetT = xRStr
End Function
Function RupeeFormat_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
RupeeFormat_GetD = xArr_1(Val(xDStr) - 1)
Else
RupeeFormat_GetD = ""
End If
End Function

``````

This comment was minimized by the moderator on the site
Thanks a lot... 🙂
This comment was minimized by the moderator on the site
Thanku for the coding. I applied & its Working. But its only apply for that particular workbook only. I Need to apply all Excell Workbook. How to apply this coding to all excel workbook.
This comment was minimized by the moderator on the site
Hello，SivaG
Yes, this code is User Defined Function, you should apply the formula to the files one by one, if you need a code to apply to multiple workbooks, it will applied to all numbers, so all the numbers will be changed. And the code can't support undo, there are some security risks. It is not recommended to do this. Thank you!
This comment was minimized by the moderator on the site
THANK YOU FOR THE CODE...
This comment was minimized by the moderator on the site
One Lac Sixty Nine Thousand Eighty One and Forty Two Paise - Not converted properly.Also not converted the above figure when it round off.
This comment was minimized by the moderator on the site
can't read more than 10 crore.
This comment was minimized by the moderator on the site
Rupees Fourteen Thousand Eight Hundred , Seventy Five Only the last amount should have the word andexample
Rupees Fourteen Thousand Eight Hundred and Seventy Five Only
This comment was minimized by the moderator on the site
Excel crashes when the reference cell has no valve!
This comment was minimized by the moderator on the site
Hi, User,
This comment was minimized by the moderator on the site
I Need to apply all Excell Workbook. How to apply this coding to all excel workbook.
This comment was minimized by the moderator on the site
Dear Sir,

Good day,
I tested your VBA code but unfortunately its not working well on negative / minus values like -100 -10,000 / (100) (10,000).

Sir, I'm using below code for last 1 years and today I discovered the same error which is found in your VBA code too.

When I write minus -10,000 it shows on "Only Hundred Ten Thousand"
When I write positive 10,000 it shows on "Only Ten Thousand" works fine

Below example:

(10,000.99) Only Hundred Ten Thousand & 99/100 Riyals
10,000.99 Only Ten Thousand & 99/100 Riyals
(10,000,000.99) Only Hundred Ten Million & 99/100 Riyals
10,000,000.99 Only Ten Million & 99/100 Riyals
(10,000,000,000.99) Only Hundred Ten Billion & 99/100 Riyals
10,000,000,000.99 Only Ten Billion & 99/100 Riyals
(10,000,000,000,000.90) Only Hundred Ten Trillion & 90/100 Riyals
10,000,000,000,000.90 Only Ten Trillion & 90/100 Riyals

Formula =spellbilling

MY VBA CODE:

'Main Function
Function SpellBilling(ByVal MyNumber)
Dim Riyals, Halalas, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert Halalas and set MyNumber to Riyal amount.
If DecimalPlace > 0 Then
Halalas = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Riyals = Temp & Place(Count) & Riyals
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Riyals
Case ""
Riyals = "No Riyal"
Case "One"
Riyals = "Only One Riyal"
Case Else
Riyals = "Only " & Riyals
Riyals = Riyals & ""
End Select
Select Case Halalas
Case ""
Halalas = " & 00/00 Riyals"
Case "One"
Halalas = " & 01/100 Riyals"
Case "Two"
Halalas = " & 02/100 Riyals"
Case "Three"
Halalas = " & 03/100 Riyals"
Case "Four"
Halalas = " & 04/100 Riyals"
Case "Five"
Halalas = " & 05/100 Riyals"
Case "Six"
Halalas = " & 06/100 Riyals"
Case "Seven"
Halalas = " & 07/100 Riyals"
Case "Eight"
Halalas = " & 08/100 Riyals"
Case "Nine"
Halalas = " & 09/100 Riyals"
Case "Ten"
Halalas = " & 10/100 Riyals"
Case "Eleven"
Halalas = " & 11/100 Riyals"
Case "Twelve"
Halalas = " & 12/100 Riyals"
Case "Thirteen"
Halalas = " & 13/100 Riyals"
Case "Fourteen"
Halalas = " & 14/100 Riyals"
Case "Fifteen"
Halalas = " & 15/100 Riyals"
Case "Sixteen"
Halalas = " & 16/100 Riyals"
Case "Seventeen"
Halalas = " & 17/100 Riyals"
Case "Eighteen"
Halalas = " & 18/100 Riyals"
Case "Nineteen"
Halalas = " & 19/100 Riyals"
Case "Twenty "
Halalas = " & 20/100 Riyals"
Case "Twenty One"
Halalas = " & 21/100 Riyals"
Case "Twenty Two"
Halalas = " & 22/100 Riyals"
Case "Twenty Three"
Halalas = " & 23/100 Riyals"
Case "Twenty Four"
Halalas = " & 24/100 Riyals"
Case "Twenty Five"
Halalas = " & 25/100 Riyals"
Case "Twenty Six"
Halalas = " & 26/100 Riyals"
Case "Twenty Seven"
Halalas = " & 27/100 Riyals"
Case "Twenty Eight"
Halalas = " & 28/100 Riyals"
Case "Twenty Nine"
Halalas = " & 29/100 Riyals"
Case "Thirty "
Halalas = " & 30/100 Riyals"
Case "Thirty One"
Halalas = " & 31/100 Riyals"
Case "Thirty Two"
Halalas = " & 32/100 Riyals"
Case "Thirty Three"
Halalas = " & 33/100 Riyals"
Case "Thirty Four"
Halalas = " & 34/100 Riyals"
Case "Thirty Five"
Halalas = " & 35/100 Riyals"
Case "Thirty Six"
Halalas = " & 36/100 Riyals"
Case "Thirty Seven"
Halalas = " & 37/100 Riyals"
Case "Thirty Eight"
Halalas = " & 38/100 Riyals"
Case "Thirty Nine"
Halalas = " & 39/100 Riyals"
Case "Forty "
Halalas = " & 40/100 Riyals"
Case "Forty One"
Halalas = " & 41/100 Riyals"
Case "Forty Two"
Halalas = " & 42/100 Riyals"
Case "Forty Three"
Halalas = " & 43/100 Riyals"
Case "Forty Four"
Halalas = " & 44/100 Riyals"
Case "Forty Five"
Halalas = " & 45/100 Riyals"
Case "Forty Six"
Halalas = " & 46/100 Riyals"
Case "Forty Seven"
Halalas = " & 47/100 Riyals"
Case "Forty Eight"
Halalas = " & 48/100 Riyals"
Case "Forty Nine"
Halalas = " & 49/100 Riyals"
Case "Fifty "
Halalas = " & 50/100 Riyals"
Case "Fifty One"
Halalas = " & 51/100 Riyals"
Case "Fifty Two"
Halalas = " & 52/100 Riyals"
Case "Fifty Three"
Halalas = " & 53/100 Riyals"
Case "Fifty Four"
Halalas = " & 54/100 Riyals"
Case "Fifty Five"
Halalas = " & 55/100 Riyals"
Case "Fifty Six"
Halalas = " & 56/100 Riyals"
Case "Fifty Seven"
Halalas = " & 57/100 Riyals"
Case "Fifty Eight"
Halalas = " & 58/100 Riyals"
Case "Fifty Nine"
Halalas = " & 59/100 Riyals"
Case "Sixty "
Halalas = " & 60/100 Riyals"
Case "Sixty One"
Halalas = " & 61/100 Riyals"
Case "Sixty Two"
Halalas = " & 62/100 Riyals"
Case "Sixty Three"
Halalas = " & 63/100 Riyals"
Case "Sixty Four"
Halalas = " & 64/100 Riyals"
Case "Sixty Five"
Halalas = " & 65/100 Riyals"
Case "Sixty Six"
Halalas = " & 66/100 Riyals"
Case "Sixty Seven"
Halalas = " & 67/100 Riyals"
Case "Sixty Eight"
Halalas = " & 68/100 Riyals"
Case "Sixty Nine"
Halalas = " & 69/100 Riyals"
Case "Seventy "
Halalas = " & 70/100 Riyals"
Case "Seventy One"
Halalas = " & 71/100 Riyals"
Case "Seventy Two"
Halalas = " & 72/100 Riyals"
Case "Seventy Three"
Halalas = " & 73/100 Riyals"
Case "Seventy Four"
Halalas = " & 74/100 Riyals"
Case "Seventy Five"
Halalas = " & 75/100 Riyals"
Case "Seventy Six"
Halalas = " & 76/100 Riyals"
Case "Seventy Seven"
Halalas = " & 77/100 Riyals"
Case "Seventy Eight"
Halalas = " & 78/100 Riyals"
Case "Seventy Nine"
Halalas = " & 79/100 Riyals"
Case "Eighty "
Halalas = " & 80/100 Riyals"
Case "Eighty One"
Halalas = " & 81/100 Riyals"
Case "Eighty Two"
Halalas = " & 82/100 Riyals"
Case "Eighty Three"
Halalas = " & 83/100 Riyals"
Case "Eighty Four"
Halalas = " & 84/100 Riyals"
Case "Eighty Five"
Halalas = " & 85/100 Riyals"
Case "Eighty Six"
Halalas = " & 86/100 Riyals"
Case "Eighty Seven"
Halalas = " & 87/100 Riyals"
Case "Eighty Eight"
Halalas = " & 88/100 Riyals"
Case "Eighty Nine"
Halalas = " & 89/100 Riyals"
Case "Ninety "
Halalas = " & 90/100 Riyals"
Case "Ninety One"
Halalas = " & 91/100 Riyals"
Case "Ninety Two"
Halalas = " & 92/100 Riyals"
Case "Ninety Three"
Halalas = " & 93/100 Riyals"
Case "Ninety Four"
Halalas = " & 94/100 Riyals"
Case "Ninety Five"
Halalas = " & 95/100 Riyals"
Case "Ninety Six"
Halalas = " & 96/100 Riyals"
Case "Ninety Seven"
Halalas = " & 97/100 Riyals"
Case "Ninety Eight"
Halalas = " & 98/100 Riyals"
Case "Ninety Nine"
Halalas = " & 99/100 Riyals"

Case Else
Halalas = " & " & Halalas & " Halalas"
End Select
SpellBilling = Riyals & Halalas
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
This comment was minimized by the moderator on the site
Dear Sir,

Good day,

I tested above formula its not working in hyphen minus values like -100 -10,000 or (100) (10,000)

Sir, I'm using below code for last 1 years and today I discovered same error.

When I write minus -10,000 or (10,000) it shows "Only Hundred Ten Thousand"
When I write Positive 10,000 it works great "Only Ten Thousand"

Below example:

(10,000.99) Only Hundred Ten Thousand & 99/100 Riyals
10,000.99 Only Ten Thousand & 99/100 Riyals
(10,000,000.99) Only Hundred Ten Million & 99/100 Riyals
10,000,000.99 Only Ten Million & 99/100 Riyals
(10,000,000,000.99) Only Hundred Ten Billion & 99/100 Riyals
10,000,000,000.99 Only Ten Billion & 99/100 Riyals
(10,000,000,000,000.90) Only Hundred Ten Trillion & 90/100 Riyals
10,000,000,000,000.90 Only Ten Trillion & 90/100 Riyals

Formula =spellbilling

MY VBA CODE:

'Main Function
Function SpellBilling(ByVal MyNumber)
Dim Riyals, Halalas, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert Halalas and set MyNumber to Riyal amount.
If DecimalPlace > 0 Then
Halalas = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Riyals = Temp & Place(Count) & Riyals
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Riyals
Case ""
Riyals = "No Riyal"
Case "One"
Riyals = "Only One Riyal"
Case Else
Riyals = "Only " & Riyals
Riyals = Riyals & ""
End Select
Select Case Halalas
Case ""
Halalas = " & 00/00 Riyals"
Case "One"
Halalas = " & 01/100 Riyals"
Case "Two"
Halalas = " & 02/100 Riyals"
Case "Three"
Halalas = " & 03/100 Riyals"
Case "Four"
Halalas = " & 04/100 Riyals"
Case "Five"
Halalas = " & 05/100 Riyals"
Case "Six"
Halalas = " & 06/100 Riyals"
Case "Seven"
Halalas = " & 07/100 Riyals"
Case "Eight"
Halalas = " & 08/100 Riyals"
Case "Nine"
Halalas = " & 09/100 Riyals"
Case "Ten"
Halalas = " & 10/100 Riyals"
Case "Eleven"
Halalas = " & 11/100 Riyals"
Case "Twelve"
Halalas = " & 12/100 Riyals"
Case "Thirteen"
Halalas = " & 13/100 Riyals"
Case "Fourteen"
Halalas = " & 14/100 Riyals"
Case "Fifteen"
Halalas = " & 15/100 Riyals"
Case "Sixteen"
Halalas = " & 16/100 Riyals"
Case "Seventeen"
Halalas = " & 17/100 Riyals"
Case "Eighteen"
Halalas = " & 18/100 Riyals"
Case "Nineteen"
Halalas = " & 19/100 Riyals"
Case "Twenty "
Halalas = " & 20/100 Riyals"
Case "Twenty One"
Halalas = " & 21/100 Riyals"
Case "Twenty Two"
Halalas = " & 22/100 Riyals"
Case "Twenty Three"
Halalas = " & 23/100 Riyals"
Case "Twenty Four"
Halalas = " & 24/100 Riyals"
Case "Twenty Five"
Halalas = " & 25/100 Riyals"
Case "Twenty Six"
Halalas = " & 26/100 Riyals"
Case "Twenty Seven"
Halalas = " & 27/100 Riyals"
Case "Twenty Eight"
Halalas = " & 28/100 Riyals"
Case "Twenty Nine"
Halalas = " & 29/100 Riyals"
Case "Thirty "
Halalas = " & 30/100 Riyals"
Case "Thirty One"
Halalas = " & 31/100 Riyals"
Case "Thirty Two"
Halalas = " & 32/100 Riyals"
Case "Thirty Three"
Halalas = " & 33/100 Riyals"
Case "Thirty Four"
Halalas = " & 34/100 Riyals"
Case "Thirty Five"
Halalas = " & 35/100 Riyals"
Case "Thirty Six"
Halalas = " & 36/100 Riyals"
Case "Thirty Seven"
Halalas = " & 37/100 Riyals"
Case "Thirty Eight"
Halalas = " & 38/100 Riyals"
Case "Thirty Nine"
Halalas = " & 39/100 Riyals"
Case "Forty "
Halalas = " & 40/100 Riyals"
Case "Forty One"
Halalas = " & 41/100 Riyals"
Case "Forty Two"
Halalas = " & 42/100 Riyals"
Case "Forty Three"
Halalas = " & 43/100 Riyals"
Case "Forty Four"
Halalas = " & 44/100 Riyals"
Case "Forty Five"
Halalas = " & 45/100 Riyals"
Case "Forty Six"
Halalas = " & 46/100 Riyals"
Case "Forty Seven"
Halalas = " & 47/100 Riyals"
Case "Forty Eight"
Halalas = " & 48/100 Riyals"
Case "Forty Nine"
Halalas = " & 49/100 Riyals"
Case "Fifty "
Halalas = " & 50/100 Riyals"
Case "Fifty One"
Halalas = " & 51/100 Riyals"
Case "Fifty Two"
Halalas = " & 52/100 Riyals"
Case "Fifty Three"
Halalas = " & 53/100 Riyals"
Case "Fifty Four"
Halalas = " & 54/100 Riyals"
Case "Fifty Five"
Halalas = " & 55/100 Riyals"
Case "Fifty Six"
Halalas = " & 56/100 Riyals"
Case "Fifty Seven"
Halalas = " & 57/100 Riyals"
Case "Fifty Eight"
Halalas = " & 58/100 Riyals"
Case "Fifty Nine"
Halalas = " & 59/100 Riyals"
Case "Sixty "
Halalas = " & 60/100 Riyals"
Case "Sixty One"
Halalas = " & 61/100 Riyals"
Case "Sixty Two"
Halalas = " & 62/100 Riyals"
Case "Sixty Three"
Halalas = " & 63/100 Riyals"
Case "Sixty Four"
Halalas = " & 64/100 Riyals"
Case "Sixty Five"
Halalas = " & 65/100 Riyals"
Case "Sixty Six"
Halalas = " & 66/100 Riyals"
Case "Sixty Seven"
Halalas = " & 67/100 Riyals"
Case "Sixty Eight"
Halalas = " & 68/100 Riyals"
Case "Sixty Nine"
Halalas = " & 69/100 Riyals"
Case "Seventy "
Halalas = " & 70/100 Riyals"
Case "Seventy One"
Halalas = " & 71/100 Riyals"
Case "Seventy Two"
Halalas = " & 72/100 Riyals"
Case "Seventy Three"
Halalas = " & 73/100 Riyals"
Case "Seventy Four"
Halalas = " & 74/100 Riyals"
Case "Seventy Five"
Halalas = " & 75/100 Riyals"
Case "Seventy Six"
Halalas = " & 76/100 Riyals"
Case "Seventy Seven"
Halalas = " & 77/100 Riyals"
Case "Seventy Eight"
Halalas = " & 78/100 Riyals"
Case "Seventy Nine"
Halalas = " & 79/100 Riyals"
Case "Eighty "
Halalas = " & 80/100 Riyals"
Case "Eighty One"
Halalas = " & 81/100 Riyals"
Case "Eighty Two"
Halalas = " & 82/100 Riyals"
Case "Eighty Three"
Halalas = " & 83/100 Riyals"
Case "Eighty Four"
Halalas = " & 84/100 Riyals"
Case "Eighty Five"
Halalas = " & 85/100 Riyals"
Case "Eighty Six"
Halalas = " & 86/100 Riyals"
Case "Eighty Seven"
Halalas = " & 87/100 Riyals"
Case "Eighty Eight"
Halalas = " & 88/100 Riyals"
Case "Eighty Nine"
Halalas = " & 89/100 Riyals"
Case "Ninety "
Halalas = " & 90/100 Riyals"
Case "Ninety One"
Halalas = " & 91/100 Riyals"
Case "Ninety Two"
Halalas = " & 92/100 Riyals"
Case "Ninety Three"
Halalas = " & 93/100 Riyals"
Case "Ninety Four"
Halalas = " & 94/100 Riyals"
Case "Ninety Five"
Halalas = " & 95/100 Riyals"
Case "Ninety Six"
Halalas = " & 96/100 Riyals"
Case "Ninety Seven"
Halalas = " & 97/100 Riyals"
Case "Ninety Eight"
Halalas = " & 98/100 Riyals"
Case "Ninety Nine"
Halalas = " & 99/100 Riyals"

Case Else
Halalas = " & " & Halalas & " Halalas"
End Select
SpellBilling = Riyals & Halalas
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
This comment was minimized by the moderator on the site
Nothing to say! Extremlysuper
There are no comments posted here yet