AutoConvert digit numbers to text word like Cheque validation

[Rudy Escoto]

More than a decade ago I created a Lotus and Excel
worksheets that converts a number digit input into a text.
For example, if I enter a number 1234.56 in cell A4, my
formula in B4 will convert or display its text equivalent
as "One Thousand Two Hundred Thirty Four and 56/100". The
maximum number is 15 digits as 999999999999999.99.

I need to know from this workgroup or others who has been
able to translate and practically apply in the real world.
And, was there any late versions having new functions to
display such text?
And in MS Access, too?
Is there any particular group interested in this function?

Rudy Escoto

[David McRitchie]

Hi Rudy,
Q213360 - XL2000: How to Convert a Numeric Value into English Words
http://support.microsoft.com/support/kb/articles/Q213/3/60.ASP
See KB Article Number Q140704 in Dollars and Cents
http://support.microsoft.com/support/kb/articles/q140/7/04.asp
See KB Article Number Q95640 One Thousand Two Hundred Thirty Four Dollars And Fifty Six Cents
http://support.microsoft.com/support/kb/articles/q95/6/40.asp

The VBA macros above can be used in Access or in Excel.

Related information: (in my strings.htm)
To protect numbers format as: _($**#,##0.00_);_($**(#,##0.00);_($**"-"??_);_(@_)

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Rudy Escoto" <noSpam~ree12345@hotmaiDOTcom> wrote in message news:9bf601c11a98$bf634630$9ee62ecf@tkmsftngxa05...

[Bernie Deitrick]

Rudy,

Below are two functions, used like

=Dollars(111.11, TRUE)

to return

One Hundred Eleven Dollars And 11/100

or like

=Dollars(111.11, FALSE)
or
=Dollars(A1, FALSE)

to return

One Hundred Eleven And 11/100

It will work up to Trillions.

HTH,
Bernie

Function Dollars(n As Double, useword As Boolean) As String
Dim myLength As Integer
Dim i As Integer
Dim myNum As Integer
Dim Remainder As Integer

Dollars = ""
Remainder = Round(100 * (n - Int(n)), 0)

myLength = Int(Application.Log10(n) / 3)

For i = myLength To 0 Step -1
myNum = Int(n / 10 ^ (i * 3))
n = n - myNum * 10 ^ (i * 3)
If myNum > 0 Then
Dollars = Dollars & MakeWord(Int(myNum)) & _
Choose(i + 1, "", " thousand ", " million ", " billion ", " trillion
")
End If
Next i
Dollars = Dollars & IIf(useword, " Dollars", "") & " and " &
Format(Remainder, "00") & "/100"
Dollars = Application.WorksheetFunction.Proper(Trim(Dollars))
End Function
Function MakeWord(inValue As Integer) As String
unitWord = Array("", "one", "two", "three", "four", "five", _
"six", "seven", "eight", "nine", "ten", "eleven", _
"twelve", "thirteen", "fourteen", "fifteen", "sixteen", _
"seventeen", "eighteen", "nineteen")
tenWord = Array("", "ten", "twenty", "thirty", "forty", "fifty", _
"sixty", "seventy", "eighty", "ninety")
MakeWord = ""
n = inValue
If n = 0 Then
MakeWord = "zero"
End If
hund = n \ 100
If hund > 0 Then
MakeWord = MakeWord & MakeWord(Int(hund)) & " hundred "
End If
n = n - hund * 100
If n < 20 Then
ten = n
MakeWord = MakeWord & unitWord(ten) & " "
Else
ten = n \ 10
MakeWord = MakeWord & tenWord(ten) & " "
unit = n - ten * 10
MakeWord = Trim(MakeWord & unitWord(unit))
End If
MakeWord = Application.WorksheetFunction.Proper(Trim(MakeWord))
End Function

[Rudy Escoto]

My worksheets absolutely involved no VBA or scripts
codeing, all involve lookup formula...
oh..anyhow, thanks for the reference.

>.
>