Skip to main content

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:

Click to Download Kutools for Excel and free trial Now!

 

  • 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...
kte tab 201905
  • 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!
officetab bottom

 

Comments (24)
No ratings yet. Be the first to rate!
AlFard
AlFard
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.
Reply
0
0
Karthik5326
Karthik5326
This comment was minimized by the moderator on the site
Can u pls share the code that would spell number for 9000 crore
Reply
Report
0
0
K NAGESH
K NAGESH
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!!"
Reply
0
0
Akanksha Vishwakarma
Akanksha Vishwakarma
This comment was minimized by the moderator on the site
Tried multiple times it's not working. Please help
Reply
0
0
Mukesh KUmar
Mukesh KUmar
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 ?
Reply
0
0
skyyang
skyyang     Mukesh KUmar
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!
Reply
Report
0
0
ADITYA R PATWARDHAN
ADITYA R PATWARDHAN
This comment was minimized by the moderator on the site
Thanks a lot it is very Helpful
Reply
0
0
prashant Mestry
prashant Mestry
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
please revert
Reply
0
0
skyyang
skyyang     prashant Mestry
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. 😀
Please try, thank you!
Reply
Report
0
0
Sakariya
Sakariya
This comment was minimized by the moderator on the site
How can remove the word "Rupees"? Normally cheque leaves have the word "Rupees" already.
Reply
0
0
skyyang
skyyang     Sakariya
This comment was minimized by the moderator on the site
Hello, Sakariya
To solve your problem, please apply the below code:
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


Please try, hope it can help you!
Reply
Report
0
0
Sakariya
Sakariya     skyyang
This comment was minimized by the moderator on the site
Thanks a lot... 🙂
Reply
0
0
SivaG
SivaG
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.
Reply
0
0
skyyang
skyyang     SivaG
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!
Reply
Report
0
0
SANS
SANS
This comment was minimized by the moderator on the site
THANK YOU FOR THE CODE...
Reply
0
0
Gol Mahmud
Gol Mahmud
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.
Reply
0
0
biren
biren
This comment was minimized by the moderator on the site
can't read more than 10 crore.
Reply
0
0
apal
apal
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
Reply
0
0
User
User
This comment was minimized by the moderator on the site
Excel crashes when the reference cell has no valve!
Reply
0
0
skyyang
skyyang     User
This comment was minimized by the moderator on the site
Hi, User,
Thank you for your comment, the code in this article has been updated, please try again, thank you!
Reply
Report
0
0
DILIP KUMAR RS
DILIP KUMAR RS     skyyang
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.
Reply
0
0
ipi99
ipi99     skyyang
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
Reply
Report
0
0
Raza
Raza     skyyang
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
Reply
0
0
KUMARESAN
KUMARESAN     Raza
This comment was minimized by the moderator on the site
Nothing to say! Extremlysuper
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations