Convert number to words

[Jeff]

I would like to change numbers from numerals (ie 258) to
words (Two Hundred Fifty-Eight.)

I would prefer to do this with Access, but would be
satisfied in Excel.

Thanks for any help.

Jeff

 

[Phil Stanton]

Hi Jeff

This was downloaded from somewhere

Hope it helps

Phil

'This function converts numbers to words. For example 101 -> One hundred
and one
'It uses standard english notation and will only accept positive long
numbers
Dim billionpart As Long
Dim millionpart As Long
billionpart = Int(OrigNum / 1000000000)
millionpart = OrigNum Mod 1000000000
NumberToWords = HundredsToWords(billionpart) & IIf(billionpart <> 0, "
billion", "")
If millionpart > 99 Then
NumberToWords = NumberToWords & IIf(millionpart <> 0 And billionpart
<> 0, " ", "") & millionstowords(millionpart)
Else
NumberToWords = NumberToWords & IIf(millionpart <> 0 And billionpart
<> 0, " and ", "") & millionstowords(millionpart)
End If


End Function
Public Function millionstowords(millionnumber As Long)
Dim millionpart As Long
Dim thousandpart As Long
millionpart = Int(millionnumber / 1000000)
thousandpart = millionnumber Mod 1000000
millionstowords = HundredsToWords(millionpart) & IIf(millionpart <> 0, "
million", "")
If thousandpart > 99 Then
millionstowords = millionstowords & IIf(thousandpart <> 0 And
millionpart <> 0, " ", "") & thousandstowords(thousandpart)
Else
millionstowords = millionstowords & IIf(thousandpart <> 0 And
millionpart <> 0, " and ", "") & thousandstowords(thousandpart)
End If
End Function
Public Function thousandstowords(thousandnumber As Long) As String
Dim thousandpart As Long
Dim HundredPart As Long
HundredPart = thousandnumber Mod 1000
thousandpart = Int(thousandnumber / 1000)
thousandstowords = HundredsToWords(thousandpart) & IIf(thousandpart <>
0, " thousand", "")
If HundredPart > 99 Then
thousandstowords = thousandstowords & IIf(HundredPart <> 0 And
thousandpart <> 0, " ", "") & HundredsToWords(HundredPart)
Else
thousandstowords = thousandstowords & IIf(HundredPart <> 0 And
thousandpart <> 0, " and ", "") & HundredsToWords(HundredPart)
End If
End Function

Public Function HundredsToWords(HundredNumber As Long) As String
'This function converts a three digit long to the hundred wording
Dim TensPart As Long
Dim HundredPart As Long
TensPart = HundredNumber Mod 100
HundredPart = Int(HundredNumber / 100)
Select Case HundredPart
Case 0
HundredsToWords = TensToWords(TensPart)
Case Else
HundredsToWords = SingleToWord(HundredPart) & " Hundred" &
IIf(TensPart <> 0, " and ", "") & TensToWords(TensPart)
End Select
End Function

Public Function TensToWords(TensNumber As Long) As String
'This function converts a two digit long to a two digit wording
Dim tens As Long
Dim Singles As Long
tens = Int(TensNumber / 10)
Singles = TensNumber Mod 10
Select Case tens
Case 0
TensToWords = SingleToWord(Singles)
Case 1
TensToWords = teens(TensNumber)
Case 2
TensToWords = "Twenty" & IIf(Singles <> 0, " ", "") &
SingleToWord(Singles)
Case 3
TensToWords = "Thirty" & IIf(Singles <> 0, " ", "") &
SingleToWord(Singles)
Case 4
TensToWords = "Fourty" & IIf(Singles <> 0, " ", "") &
SingleToWord(Singles)
Case 5
TensToWords = "Fifty" & IIf(Singles <> 0, " ", "") &
SingleToWord(Singles)
Case 6
TensToWords = "Sixty" & IIf(Singles <> 0, " ", "") &
SingleToWord(Singles)
Case 7
TensToWords = "Seventy" & IIf(Singles <> 0, " ", "") &
SingleToWord(Singles)
Case 8
TensToWords = "Eighty" & IIf(Singles <> 0, " ", "") &
SingleToWord(Singles)
Case 9
TensToWords = "Ninety" & IIf(Singles <> 0, " ", "") &
SingleToWord(Singles)
End Select
End Function

Public Function SingleToWord(SingleDigit As Long) As String
Select Case SingleDigit
Case 1
SingleToWord = "One"
Case 2
SingleToWord = "Two"
Case 3
SingleToWord = "Three"
Case 4
SingleToWord = "Four"
Case 5
SingleToWord = "Five"
Case 6
SingleToWord = "Six"
Case 7
SingleToWord = "Seven"
Case 8
SingleToWord = "Eight"
Case 9
SingleToWord = "Nine"
Case 0
SingleToWord = ""
End Select
End Function

Public Function teens(TeenNumber As Long) As String
Select Case TeenNumber
Case 10
teens = "Ten"
Case 11
teens = "Eleven"
Case 12
teens = "Twelve"
Case 13
teens = "Thirteen"
Case 14
teens = "Fourteen"
Case 15
teens = "Fifteen"
Case 16
teens = "Sixteen"
Case 17
teens = "Seventeen"
Case 18
teens = "Eighteen"
Case 19
teens = "Nineteen"
End Select
End Function

 

[Tom Wickerath]

In addition to Phil's contribution, here are some other methods that you can use:

Convert Currency ($500) into words (Five Hundred Dollars) by Joe Foster
http://www.mvps.org/access/modules/mdl0001.htm

and

How to Convert Currency or Numbers into English Words (Microsoft KB article)
http://support.microsoft.com/?id=210586

Also, see related KB articles # 234302 (for DAP's) and 95640 (for Access versions 1-->7).


Note that for either of these methods, you do not want to include commas in the number. For
example, from the Immediate window, the commands:

? English(2,500,500.23) or ? ConvertCurrencyToEnglish(2,500,500.23)

will produce a run-time error: "Wrong number of arguments or invalid property assignment",
whereas the commands:

? English(2500500.23) or ? ConvertCurrencyToEnglish(2500500.23)

will return the results:
"two million five hundred thousand five hundred and 23/100" and
"Two Million Five Hundred Thousand Five Hundred Dollars And Twenty Three Cents", respectively.


Tom
_________________________________


I would like to change numbers from numerals (ie 258) to
words (Two Hundred Fifty-Eight.)

I would prefer to do this with Access, but would be
satisfied in Excel.

Thanks for any help.

Jeff