Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Yet Another Number-To-Words Function (Sorry, US Style Only)

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    Yet Another Number-To-Words Function (Sorry, US Style Only)

    You are probably wondering, "Why on earth is he posting another number-to-words function?" Well, I think mine is slightly different than those you have seen before. For one thing, my function can handle very large numbers... a fraction less than a quintillion. That is a number with 18 digits in front of the decimal point! For another thing, you can post your output in any one of four different way. Anyway, follow these instructions and try it out... if you don't like it, you can always delete it. And, of course, this function can be called from your other VB code or it can be used as a UDF (user defined function) in worksheet formulas.

    Go into the VB Editor (Alt+F11) and add a Module to the Workbook (Insert/Module from the VBA menu) and then paste in all of the code appearing below into the Module's code window. You can call the function from your work sheet like this =NumberAsText(A1) where A1 is assumed to hold the number you want to convert. There is an Optional argument you can use to format the results to your liking. Here are some examples to show the options available...

    A1: 123.45

    In B1:
    --------------
    =NumberAsText(A1) ==> One Hundred Twenty Three Point Four Five
    =NumberAsText(A1,"And") ==> One Hundred and Twenty Three Point Four Five
    =NumberAsText(A1,"Check") ==> One Hundred Twenty Three and 45/100
    =NumberAsText(A1,"Dollar") ==> One Hundred Twenty Three Dollars and Forty Five Cents
    =NumberAsText(A1,"CheckDollar") ==> One Hundred Twenty Three Dollars and 45/100

    To summarize, using "And" adds the word "and" in front of the tens/units text; using "Check" formats any decimal values as would be written on a check; using "Dollar" adds the words "Dollars" and "Cents" in their appropriate positions; and using "CheckDollar" adds the word "Dollars" but leaves the decimal portions as a fraction of a dollar.

    The code is laid out in a reasonably straight forward manner, so if Dollars/Cents is not you native currency designation, you might be able to modify the program to fit your own units. In addition to the above, for all modes, the Plus and Minus sign can be used and will be reported back as a word; commas may be used to separate the numbers to the left of the decimal point but they will not be reported back by the routine and are permitted for the users convenience (however, if commas are used, they must be placed in their correct positions). If you specify an option that includes words, those words are properly pluralize (meaning 1 reports as One Dollar and not One Dollars). And, as I said earlier, this function will work with a whole number part up to one less than a quintillion (you can have as many decimal places after the decimal point as desired), but remember to pass in very large numbers as Text... otherwise they will end up being converted to Doubles (which will destroy the conversion).

    Code:
    Private sNumberText() As String
    
    Public Function NumberAsText(NumberIn As Variant, Optional _
                    AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR As String) As String
       Dim cnt As Long
       Dim DecimalPoint As Long
       Dim CardinalNumber As Long
       Dim CommaAdjuster As Long
       Dim TestValue As Long
       Dim CurrValue As Currency
       Dim CentsString As String
       Dim NumberSign As String
       Dim WholePart As String
       Dim BigWholePart As String
       Dim DecimalPart As String
       Dim tmp As String
       Dim sStyle As String
       Dim bUseAnd As Boolean
       Dim bUseCheck As Boolean
       Dim bUseDollars As Boolean
       Dim bUseCheckDollar As Boolean
      '----------------------------------------
      '  Begin setting conditions for formatting
      '----------------------------------------
       sStyle = LCase(AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR)
       bUseAnd = sStyle = "and"
       bUseDollars = sStyle = "dollar"
       bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
       bUseCheckDollar = sStyle = "checkdollar"
      '----------------------------------------
      '  Check/create array. If this is the first
      '  time using this routine, create the text
      '  strings that will be used.
      '----------------------------------------
       If Not IsBounded(sNumberText) Then
          Call BuildArray(sNumberText)
       End If
      '----------------------------------------
      '  Begin validating the number, and breaking
      '  into constituent parts
      '----------------------------------------
       NumberIn = Trim$(NumberIn)
       If Not IsNumeric(NumberIn) Then
          NumberAsText = "Error - Number improperly formed"
          Exit Function
       Else
          DecimalPoint = InStr(NumberIn, ".")
          If DecimalPoint > 0 Then
             DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
             WholePart = Left$(NumberIn, DecimalPoint - 1)
          Else
             DecimalPoint = Len(NumberIn) + 1
             WholePart = NumberIn
          End If
          If InStr(NumberIn, ",,") Or _
             InStr(NumberIn, ",.") Or _
             InStr(NumberIn, ".,") Or _
             InStr(DecimalPart, ",") Then
             NumberAsText = "Error - Improper use of commas"
             Exit Function
          ElseIf InStr(NumberIn, ",") Then
             CommaAdjuster = 0
             WholePart = ""
             For cnt = DecimalPoint - 1 To 1 Step -1
                If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
                   WholePart = Mid$(NumberIn, cnt, 1) & WholePart
                Else
                   CommaAdjuster = CommaAdjuster + 1
                   If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
                      NumberAsText = "Error - Improper use of commas"
                      Exit Function
                   End If
                End If
             Next
          End If
       End If
       If Left$(WholePart, 1) Like "[+-]" Then
          NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
          WholePart = Mid$(WholePart, 2)
       End If
      '----------------------------------------
      '  Begin code to assure decimal portion of
      '  check value is not inadvertently rounded
      '----------------------------------------
       If bUseCheck = True Then
          CurrValue = CCur(Val("." & DecimalPart))
          DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
          If CurrValue >= 0.995 Then
             If WholePart = String$(Len(WholePart), "9") Then
                WholePart = "1" & String$(Len(WholePart), "0")
             Else
                For cnt = Len(WholePart) To 1 Step -1
                  If Mid$(WholePart, cnt, 1) = "9" Then
                     Mid$(WholePart, cnt, 1) = "0"
                  Else
                     Mid$(WholePart, cnt, 1) = _
                                CStr(Val(Mid$(WholePart, cnt, 1)) + 1)
                     Exit For
                  End If
                Next
             End If
          End If
       End If
      '----------------------------------------
      '  Final prep step - this assures number
      '  within range of formatting code below
      '----------------------------------------
       If Len(WholePart) > 9 Then
          BigWholePart = Left$(WholePart, Len(WholePart) - 9)
          WholePart = Right$(WholePart, 9)
       End If
       If Len(BigWholePart) > 9 Then
          NumberAsText = "Error - Number too large"
          Exit Function
       ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
             (Not BigWholePart Like String$(Len(BigWholePart), "#") _
              And Len(BigWholePart) > 0) Then
          NumberAsText = "Error - Number improperly formed"
          Exit Function
       End If
      '----------------------------------------
      '  Begin creating the output string
      '----------------------------------------
      '  Very Large values
       TestValue = Val(BigWholePart)
       If TestValue > 999999 Then
          CardinalNumber = TestValue \ 1000000
          tmp = HundredsTensUnits(CardinalNumber) & "Quadrillion "
          TestValue = TestValue - (CardinalNumber * 1000000)
       End If
       If TestValue > 999 Then
         CardinalNumber = TestValue \ 1000
         tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
         TestValue = TestValue - (CardinalNumber * 1000)
       End If
       If TestValue > 0 Then
          tmp = tmp & HundredsTensUnits(TestValue) & "Billion "
       End If
      '  Lesser values
       TestValue = Val(WholePart)
       If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
       If TestValue > 999999 Then
          CardinalNumber = TestValue \ 1000000
          tmp = tmp & HundredsTensUnits(CardinalNumber) & "Million "
          TestValue = TestValue - (CardinalNumber * 1000000)
       End If
       If TestValue > 999 Then
          CardinalNumber = TestValue \ 1000
          tmp = tmp & HundredsTensUnits(CardinalNumber) & "Thousand "
          TestValue = TestValue - (CardinalNumber * 1000)
       End If
       If TestValue > 0 Then
          If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
          tmp = tmp & HundredsTensUnits(TestValue, bUseAnd)
       End If
      '  If in dollar mode, assure the text is the correct plurality
       If bUseDollars = True Then
          CentsString = HundredsTensUnits(DecimalPart)
          If tmp = "One " Then
             tmp = tmp & "Dollar"
          Else
             tmp = tmp & "Dollars"
          End If
          If Len(CentsString) > 0 Then
             tmp = tmp & " and " & CentsString
             If CentsString = "One " Then
                tmp = tmp & "Cent"
             Else
                tmp = tmp & "Cents"
             End If
          End If
       ElseIf bUseCheck = True Then
          tmp = tmp & "and " & Left$(DecimalPart & "00", 2)
          tmp = tmp & "/100"
       ElseIf bUseCheckDollar = True Then
          If tmp = "One " Then
             tmp = tmp & "Dollar"
          Else
             tmp = tmp & "Dollars"
          End If
          tmp = tmp & " and " & Left$(DecimalPart & "00", 2)
          tmp = tmp & "/100"
       Else
          If Len(DecimalPart) > 0 Then
            tmp = tmp & "Point"
            For cnt = 1 To Len(DecimalPart)
              tmp = tmp & " " & sNumberText(Mid$(DecimalPart, cnt, 1))
            Next
          End If
       End If
      '  Done!
       NumberAsText = NumberSign & tmp
    End Function
    
    Private Sub BuildArray(sNumberText() As String)
       ReDim sNumberText(0 To 27) As String
       sNumberText(0) = "Zero"
       sNumberText(1) = "One"
       sNumberText(2) = "Two"
       sNumberText(3) = "Three"
       sNumberText(4) = "Four"
       sNumberText(5) = "Five"
       sNumberText(6) = "Six"
       sNumberText(7) = "Seven"
       sNumberText(8) = "Eight"
       sNumberText(9) = "Nine"
       sNumberText(10) = "Ten"
       sNumberText(11) = "Eleven"
       sNumberText(12) = "Twelve"
       sNumberText(13) = "Thirteen"
       sNumberText(14) = "Fourteen"
       sNumberText(15) = "Fifteen"
       sNumberText(16) = "Sixteen"
       sNumberText(17) = "Seventeen"
       sNumberText(18) = "Eighteen"
       sNumberText(19) = "Nineteen"
       sNumberText(20) = "Twenty"
       sNumberText(21) = "Thirty"
       sNumberText(22) = "Forty"
       sNumberText(23) = "Fifty"
       sNumberText(24) = "Sixty"
       sNumberText(25) = "Seventy"
       sNumberText(26) = "Eighty"
       sNumberText(27) = "Ninety"
    End Sub
    
    Private Function IsBounded(vntArray As Variant) As Boolean
       On Error Resume Next
       IsBounded = IsNumeric(UBound(vntArray))
    End Function
    
    Private Function HundredsTensUnits(ByVal TestValue As Integer, _
                                  Optional bUseAnd As Boolean) As String
       Dim CardinalNumber As Integer
       If TestValue > 99 Then
          CardinalNumber = TestValue \ 100
          HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
          TestValue = TestValue - (CardinalNumber * 100)
       End If
       If bUseAnd = True Then
          HundredsTensUnits = HundredsTensUnits & "and "
       End If
       If TestValue > 20 Then
          CardinalNumber = TestValue \ 10
          HundredsTensUnits = HundredsTensUnits & _
                              sNumberText(CardinalNumber + 18) & " "
          TestValue = TestValue - (CardinalNumber * 10)
       End If
       If TestValue > 0 Then
          HundredsTensUnits = HundredsTensUnits & _
                              sNumberText(TestValue) & " "
       End If
    End Function

    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumberAsText just like it was a built-in Excel function. For example,

    =NumberAsText(A1)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Last edited by Rick Rothstein; 12-29-2018 at 10:24 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Last edited by DocAElstein; 06-10-2023 at 01:35 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Apr 2012
    Posts
    5
    Rep Power
    0

    Rick you are a magician

    I have become a follower and fan of yours VBA capabilities. You are a magician

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    @vikash200418

    You are much too kind in your praises of my VBA capabilities, but I do appreciate them all the same... thank you very much.

  5. #5
    Junior Member
    Join Date
    Apr 2012
    Posts
    5
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    @vikash200418

    You are much too kind in your praises of my VBA capabilities, but I do appreciate them all the same... thank you very much.
    Rick,

    I have just appreciate your capabilities and I am proud of it.

    To you earlier macro which you have given me

    I just want an edit in the following macro (which I am sure is very small for you) whereby after running the macro, it asks for the cell from where to copy the data instead of taking the origination as active cell before the input box for "how many repeats"

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    I wrote a function in dutch.
    After some minor adjustments it is also applicable to the English language (correct me if I'm wrong)

    The simplest version I put here: 2 functions & 1 macro.
    For more elaborate functions, including decimal numbers & formatted numbers see

    http://www.snb-vba.eu/VBA_Getallen_naar_tekst_en.html

    Code:
    Function F_mats(y)
      On Error Resume Next
        
      F_mats = Split(Split(" 0 1One 2Two 3Three 4Four 5Five 6Six 7Seven 8Eight 9Nine 10Ten 11Eleven 12Twelve 13Thirteen 15Fifteen 20Twenty 30Thirty 50Fifty 80Eighty ", y)(1))(0)
    End Function
    
    Function F_convert(y)
      F_convert = "invalid input"
      If y = "" Or Val(y) = 0 Then Exit Function
        
      c00 = Format(Val(1 * y), String(3 * ((Len(Format(Val(1 * y))) - 1) \ 3 + 1), "0"))
        
      For j = 1 To Len(c00) \ 3
        x = Mid(c00, 3 * (j - 1) + 1, 3)
            
        sp = Array(F_mats(Left(x, 1)), F_mats(Val(Right(x, 2))), F_mats(Right(x, 1)), F_mats(Mid(x, 2, 1) & "0"), F_mats(Mid(x, 2, 1)))
        c01 = c01 & IIf(sp(0) = "", "", sp(0) & " Hundred ") & IIf(Right(x, 2) = "00", "", IIf(sp(1) <> "", sp(1), IIf(Mid(x, 2, 1) = "1", Trim(sp(2)) & "teen", IIf(sp(3) = "", sp(4) & "ty", sp(3)) & " " & sp(2)))) & Choose(Len(c00) \ 3 - j + 1, "", " Thousand ", " Million ", " Billion ")
      Next
        
      F_convert = IIf(c01 = "", "zero", Replace(c01, "  ", " "))
    End Function
    
    Sub M_tst()
      MsgBox F_convert(InputBox(String(4, vbLf) & "enter a number", "snb")), , "snb"
    End Sub

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 06-10-2023 at 01:35 PM.

  7. #7
    Junior Member
    Join Date
    May 2015
    Posts
    1
    Rep Power
    0

    Your help is much appreciated

    what if I have 123.20 and convert it into *** ONE HUNDRED TWENTY-THREE AND 20/100 PESOS ONLY ***,

    Thank you for your effort in helping us..

  8. #8
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    SNB,
    I know it's years later, but I just ran across this post. Testing your function, if I input "451" I get a proper response of "Four Hundred Fifty One". However, if I input "451.27", I get "Thousand Four Hundred Fifty One"

    EDIT: Rick, I saw your note to me, but can't find it on this thread. Note that it's not your function that gave the invalid result, it was the alternative that SNB posted. Have you tested that?
    Last edited by jomili; 06-16-2016 at 02:13 AM.

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    @jomili

    I can only refer you to the 4th (fourth) line I wrote in my post.
    The code I posted is restricted to non-decimals to illustrate the principle.

  10. #10
    Junior Member
    Join Date
    Aug 2020
    Posts
    2
    Rep Power
    0
    Dear Sir,

    Good day,

    I've tested your VBA code it works great 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 minus value error. I tried to modify you VBA code but unfortunately I couldn't coz I'm not very familiar with VBA code Could you please correct my VBA code I'll be very thankful to you.

    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:

    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
    Last edited by DocAElstein; 08-05-2020 at 08:31 PM.

Similar Threads

  1. Number into words (Rupees)
    By Admin in forum Download Center
    Replies: 2
    Last Post: 06-14-2014, 12:05 AM
  2. Number to Words (Rupees)
    By vishwajeet_chakravorty in forum Excel Help
    Replies: 8
    Last Post: 02-24-2014, 09:26 PM
  3. Excel Number Format: Indian Style Comma Separation
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 09-18-2013, 11:38 AM
  4. Follow-up to "Excel Number Format: Indian Style Comma Separation"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 04-14-2012, 10:46 PM
  5. Number to Words (Rupees)
    By sa.1985 in forum Excel Help
    Replies: 2
    Last Post: 12-16-2011, 08:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •