how to transit amount in figures into amount in words

[garfunkel]

example,
"USD123.45" into
"SAY US DOLLARS ONE HUNDRED TWENTY THREE AND FORTY FIVE
CENTS ONLY"

 

[Bob Phillips]

Garfunkel,

I'll send you a workbook example if you want. Mail me direct if so.

 

[Norman Harker]

Hi Garfunkel!
You should try a Google search for questions as you might imagine that
this is a fairly common question.
Best answer is that you should download the Google Search 5.0 Addin
and User Guide from:
Ron de Bruin:
http://www.rondebruin.nl/Google.htm
But the answer that you are directly looking for is:
If you want numbers to be translated in Thai then there's an Excel
function BAHTTEXT that will do it. For some reason Microsoft didn't
think non-Thai speakers needed an equivalent. So:
See:
XL2000: How to Convert a Numeric Value into English Words
http://support.microsoft.com/default.aspx?scid=KB;EN-US;213360
and:
XL: How to Convert a Numeric Value into English Words
http://support.microsoft.com/default.aspx?scid=KB;EN-US;140704&
and:
(courtesy of a cut and paste from a Tom Ogilvy post):
If you want an addin that provides a worksheet function that does
this, download Laurent Longre's free morefunc.xll addin found here:
http://longre.free.fr/english/
It is downloaded in a zip file which also contains an informative file
in 'hlp' format that describes the 33 or so very useful functions
included, one of which does the number to words conversion you
describe (supports various languages and currencies).
See also Laurent's latest version that is multi-lingual.
http://longre.free.fr/tradnombre/NbText.zip
and:
(Courtesy of Andy Wiggins FCCA) of www.BygSoftware.com
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/num2wrds.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm
It contains two methods to convert numbers to words and two check
writing routines.
The code is open and commented.
And:
http://groups.google.com/[email protected]
A post containing a UDF by Bernie Deitrick that will take you into US
budget territory by covering amounts into trillions.
And:
Orlando Magalhaes Filho (MVP)
http://cpap.com.br/orlando/#SpellNumber
A very flexible spell number function that allows, ordinary,
percentage and currency options.

Now all of that was capable of being found very quickly with a Google
Search using Ron de Bruin's Google Search 5.0

That's not intended to discourage from posting but more to illustrate
use and interaction of newsgroups with efficient Google searching.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Saturday 19th July: Andorra (Canillo), France
(Edgar Degas Birthday), Laos (Independence Day), Malaysia (Birthday of
Yand di-Pertuan Besar of Mg Sembilan), Myanmar (Martyrs' Day),
Nicaragua (Revolution / Sandinista Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[garfunkel]

Bob,Hank and Norman.
I've got the answer.
Thanks a lot.
VBA MODULE:

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, 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 cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = 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 Dollars = Temp & Place(Count) &
Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "SAY US DOLLAR ZERO"
Case "One"
Dollars = "SAY US DOLLAR ONE"
Case Else
Dollars = "SAY US DOLLARS " & Dollars
End Select
Select Case Cents
Case ""
Cents = " ONLY"
Case "One"
Cents = " AND ONE CENT ONLY"
Case Else
Cents = " AND " & Cents & " CENTS ONLY"
End Select
SpellNumber = Dollars & Cents
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 = "THIRTH "
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

 

[Norman Harker]

Hi Garfunkel!

Seems to work OK to me but don't I just hate it when people shout
about money. <vbg>

Also, my preference in real estate appraisal would be:

=spellnumber(123456)
Returns: '(Say) $US One Hundred and Twenty Three Thousand Four Hundred
and Fifty Six Dollars Only

Here I would prefer the use of something like:

="(Say) $US " & SPELLNUMBER(A1)

You'll see that some of the references pointed to look at things very
similarly to that. Having said that, in UK and Australian appraisals
the common form is:

"bla bla fairly represented by the figure of $1,250,000 (ONE MILLION
TWO HUNDRED AND FIFTY THOUSAND DOLLARS)"

We do tend to use all upper case words as a security confirmation of
the appraised value.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Sunday 20th July: Bulgaria (St. Elijah's
Day), Colombia (Independence Day), North Cyprus (Peace and Freedom
Day), Italy (Festa del Redentore), Japan (Marine Day), Norway
(Birthday of Crown Prince Haakon), Solomon Islands (Special Holiday
Renbel Province), USA (Moon Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.