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

[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

 

[Guest]

Change that last line of the first funtion to add
& "*******"
to the end, however many * you want like this
SpellNumber = Application.Proper(Trim(SpellNumber)) & "*****"

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi,
I found the VBA and placed the ********** but it did not work. What did I do
wrong?

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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!!

 

[iliace]

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.

 

[Guest]

Hi Iliace,

That worked too. Didn't know that format existed.Thanks!!