Results 1 to 5 of 5
  1. #1
    3 Star Lounger rcbjr2's Avatar
    Join Date
    Jan 2001
    Location
    Matthews, NC
    Posts
    279
    Thanks
    6
    Thanked 1 Time in 1 Post

    Macros - Numbers to Words (Word 2000/2002)

    I used to have a couple of "numbers to words" macros from the web (i.e., user types in a number, and macro adds the words, as in Two Thousand Twenty-Two and 23/100 Dollars ($2,022.23)). I thought they were something like "Say" macros, but I can't remember. I did create one myself from what I learned from these macros, but it doesn't work well for numbers over a million or for numbers that have no other dollars (e.g., 10,000, converts as Ten Thousand and Zero Dollars, when it should just be Ten Thousand Dollars). I tried perusing the headings here, but without the search facility, it's difficult to find anything (is that ever going to come back on line?). Anyway, does anybody know of any such macros? I'd like it to work for numbers, dollars, and percents if possible (mine does, but as I said, it's limited otherwise).

    Thx.

    -Rich Belthoff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macros - Numbers to Words (Word 2000/2002)

    See for instance Implementing a Number-To-Text Conversion Function on VBnet (on the MVPS site). If you would like more, a Google search should yield lots of alternatives.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Macros - Numbers to Words (Word 2000/2002)

    Hi Rich,

    Here's an Excel function I found a year or so ago (can't remember where). It will also give you something like Three Hundred Thirty Dollars and Sixty Seven Cents if you pass it the optional second and third parameters like =NumberToText(A1,"Dollars","Cents"), where A1 holds 336.67.

    Cheers

    Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional vCent As Variant) As Variant
    Dim TMBT As Variant
    Dim sNum As String, sDec As String, sHun As String, IC As Integer
    Dim Result As String, sCurName As String, sCent As String
    If Application.IsNumber(Num) = False Then
    NumberToText = CVErr(xlValue)
    Exit Function
    End If
    If IsMissing(vCurName) Then
    sCurName = ""
    Else
    sCurName = Trim(CStr(vCurName))
    End If
    If IsMissing(vCent) Then
    sCent = ""
    Else
    sCent = Trim(CStr(vCent))
    End If
    TMBT = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion")
    If IsMissing(sCent) Or IsNull(sCent) Then
    sNum = Format(Application.Round(Num, 0), "0")
    Else
    sNum = Format(Application.Round(Num, 2), "0.00")
    sDec = Right(sNum, 2)
    sNum = Left(sNum, Len(sNum) - 3)
    If CInt(sDec) <> 0 Then
    sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent)
    Else
    sDec = ""
    End If
    End If
    IC = 0
    While Len(sNum) > 0
    sHun = Right(sNum, 3)
    sNum = Left(sNum, Application.Max(Len(sNum) - 3, 0))
    If CInt(sHun) <> 0 Then
    Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " & Result)
    End If
    IC = IC + 1
    Wend
    Result = Trim(Result & " " & sCurName)
    Result = Trim(Result & " " & sDec)
    NumberToText = Result
    End Function
    Function HundredsToText(Num As Integer) As String
    Dim Units As Variant, Teens As Variant, Tens As Variant
    Dim I As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer
    Dim Result As String
    Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
    Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
    Result = ""
    IUnit = Num Mod 10
    I = Int(Num / 10)
    ITen = I Mod 10
    IHundred = Int(I / 10)
    If IHundred > 0 Then
    Result = Units(IHundred) & " Hundred"
    End If
    If ITen = 1 Then
    Result = Result & " " & Teens(IUnit)
    Else
    If ITen > 1 Then
    Result = Trim(Result & " " & Tens(ITen) & " " & Units(IUnit))
    Else
    Result = Trim(Result & " " & Units(IUnit))
    End If
    End If
    HundredsToText = Result
    End Function
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    3 Star Lounger rcbjr2's Avatar
    Join Date
    Jan 2001
    Location
    Matthews, NC
    Posts
    279
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Macros - Numbers to Words (Word 2000/2002)

    Thanks for the reply. Looks like there's other good stuff there as well.

    -Rich.

  5. #5
    3 Star Lounger rcbjr2's Avatar
    Join Date
    Jan 2001
    Location
    Matthews, NC
    Posts
    279
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Macros - Numbers to Words (Word 2000/2002)

    <P ID="edit" class=small>(Edited by jscher2000 on 03-Sep-03 15:41. For this much code, an attachment is preferred.)</P>In case anyone is interested, here is the numbers to words macro I created. It uses Word fields to display numbers. This avoids having to hard code One, Two, Three, ... Twenty, etc. As far as I can tell, it works fine, but if someone notices a problem, please let me know. You need to put your insertion point to the RIGHT of the number and then run the macro. It works for plain numbers (e.g., 100, 1.27, etc.), percents (e.g., 1.4%, 87%, etc.), or dollars (e.g., $1.47, $1,000). It will add zeroes to the right side of a decimal if appropriate. You don't have to put the commas between the numbers before you run the macro because it will add the commas. An example output is as follows: "One Million Seven Hundred Sixty-Five Thousand Three Hundred Forty-Five Dollars and Ninety-Eight Cents ($1,765,345.98)". If there are no cents, it will display as follows: "One Thousand Eight Hundred Seventy-Six Dollars and No Cents ($1,876.00)". If you want to tweak the code, you can make it not use the "and No Cents" words. Enjoy. -Rich Belthoff

    Edited: Lengthy code moved to the attachment. -Jefferson
    Attached Files Attached Files

Posting Permissions

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