Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
Change that last line of the first funtion to add
& "*******" to the end, however many * you want like this SpellNumber = Application.Proper(Trim(SpellNumber)) & "*****" -- -John Please rate when your question is answered to help us and others know what is helpful. "shapiro" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
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) "shapiro" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
Hi,
I found the VBA and placed the ********** but it did not work. What did I do wrong? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
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) "shapiro" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
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) "shapiro" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
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!! Bob Phillips" wrote: 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) "shapiro" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
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. On Aug 12, 10:14 am, shapiro wrote: 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!! Bob Phillips" wrote: 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) "shapiro" wrote in message ... 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- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert $10.20 to text ...Ten Dollars &20/100
Hi Iliace,
That worked too. Didn't know that format existed.Thanks!! "iliace" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert pesos to dollars???? | Excel Worksheet Functions | |||
How to convert Euro's to Canadian dollars? | Excel Worksheet Functions | |||
Convert $12.00 to text (Twelve dollars) like you would on a chec.. | Excel Worksheet Functions | |||
How do I convert $500 to Five Hundred Dollars? | Excel Worksheet Functions | |||
How do I convert dollars and cents to text, and prefill the cell . | Excel Worksheet Functions |