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

1. 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. 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. 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

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

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

-Rich.

5. 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

Posting Permissions

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