convert $10.20 to text ...Ten Dollars &20/100

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i All,
I found this amazing website

http://www.xldynamic.com/source/xld.xlFAQ0004.html

When you type $10.20 on the check the words spell out as Ten dollars
&20/100. It makes my personal checks look official!!! However, I would love
to have the words print as Ten Dollars & 20/100***********. I have been
playing around with the code but I am unable to put it the ********. Does
anyone know how to do this?
Thanks a mil
 
Change that last line of the first funtion to add
& "*******"
to the end, however many * you want like this
SpellNumber = Application.Proper(Trim(SpellNumber)) & "*****"
 
Which method did you use, there are a few there

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi John,

I used =SpellNumber(A1,TRUE,"Pounds","",",",TRUE) and the VBA code for the
URL. Because it was the only code that had the fraction i.e. 45/100
 
Hi Bob,

I used =SpellNumber(A1,TRUE,"Pounds","",",",TRUE) and the VBA code for the
URL. Because it was the only code that had the fraction i.e. 45/100
 
Hi,
I found the VBA and placed the ********** but it did not work. What did I do
wrong?
 
Option Explicit
Function SpellNumber(ByVal n As Double, _
Optional ByVal useword As Boolean = True, _
Optional ByVal ccy As String = "Dollars", _
Optional ByVal cents As String = "", _
Optional ByVal join As String = " And", _
Optional ByVal fraction As Boolean = False, _
Optional PrintLen As Long) As String
Dim myLength As Long
Dim i As Long
Dim myNum As Long
Dim Remainder As Long

SpellNumber = ""
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
SpellNumber = SpellNumber & MakeWord(Int(myNum)) & _
Choose(i + 1, "", " thousand ", " million ", " billion ", "
trillion")
End If
Next i
SpellNumber = SpellNumber & IIf(useword, " " & ccy, "") & _
IIf(Remainder > 0, join & " " & Format(Remainder, "00"),
" Only") & _
IIf(fraction, "/100", "") & " " & cents
SpellNumber = Application.Proper(Trim(SpellNumber))
If PrintLen > 0 Then
If Len(SpellNumber) < PrintLen Then
SpellNumber = SpellNumber &
Left$("********************************", _
PrintLen - Len(SpellNumber))
End If
End If

End Function

Function MakeWord(ByVal inValue As Long) As String
Dim unitWord, tenWord
Dim n As Long
Dim unit As Long, ten As Long, hund As Long

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"
hund = n \ 100
If hund > 0 Then MakeWord = MakeWord & MakeWord(Int(hund)) & " hundred "
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.Proper(Trim(MakeWord))

End Function


and use like so

=SpellNumber(A1,TRUE,"Pounds","",",",TRUE,45)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Bob,
I tried the VBA but it is not working.could the reason be that I am using
windows 2003?
Below is a part of the code I am having problems with. The lines that are
tabbed in by an inch show up as red in my VBA Code

For i = myLength To 0 Step -1
myNum = Int(n / 10 ^ (i * 3))
n = n - myNum * 10 ^ (i * 3)
If myNum > 0 Then
SpellNumber = SpellNumber & MakeWord(Int(myNum)) & _
Choose(i + 1, "", " thousand ", " million ", " billion ", "
trillion ") "
End If
Next i
SpellNumber = SpellNumber & IIf(useword, " " & ccy, "") & _
IIf(Remainder > 0, join & " " & Format(Remainder, "00"),
" Only") & _
IIf(fraction, "/100", "") & " " & cents
SpellNumber = Application.Proper(Trim(SpellNumber))
If PrintLen > 0 Then
If Len(SpellNumber) < PrintLen Then
SpellNumber = SpellNumber &
Left$("********************************", _
PrintLen - Len(SpellNumber))
End If
End If
 
Nothing was tabbed in the reader that I am using. Put a signal before and
after. Tell me what error you get as well.

I tried it on XP and it worked fine there. 2003 shouldn't make a difference.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Bob,
It worked!!! When I copied your VBA,the lines were pasted in a broken
fashion.That's why I got the error message. I tried to tab the lines with
errors in my earlier explaination but I guess after I submitted my response
the tab was removed Thanks again Bob. That was Awesome!!
 
I'm not sure whether this will work, but why don't you just set the
cell's custom number format to "@ **"? This will fill the cell with *
characters following the spelled dollar amount, and will always align
to cell's width.
 
Back
Top