Number to text

[Neville]

I know this one is dead easy but...

I want to convert number eg

87200

to Eighty seven thousand and two hundred dollars for printing on documents

I had the code to do it (Apple in those days, about 6 years ago)) faxed it to me but I didn't use it at the time.

I need it now and went to get the fax out of the file

(we all know what happens to thermal faxes - They go blank over years don't they!)

So dont know how to do it

I'n not sure if it was a field or a scrip step

help appreciated.

Neville

[andygaunt]

Hi,

I dredged through my filemaker files and found this solution courtesy of Bob Cusick of www.clickware.com.

If you're using FileMaker to print checks, you can have FileMaker automatically convert the dollar (number) amount of the check into text (words). To do this, follow this procedure:

Define a new field, Amt, as a number field containing a dollar amount. If your database already contains a dollar amount field that you want to use, first rename it to Amt. Then define the remaining fields in the list. You should type in the field names and definitions exactly as shown with one exception: some field definitions are shown containing a caret (^). Instead, you should type in spaces where these occur in the formulas. The caret is used for display purposes only.

These fields will convert to text any amount under one million dollars.

Field Name Field Type Formula / Entry Option

Amt Number

Tn Calc (Number) = Int (mod (Amt,100))

Hd Calc (Number) = Int (mod (Amt,1000) / 100)

TTh Calc (Number) = Int (mod (Amt,100000) / 1000)

HT Calc (Number) = Int (Amt / 100000)

TnTx Calc (Text) = "^^^^^^^^^One^^^^^^Two^^^^^^Three^^^^Four^^^^^Five^^^^^Six^^^^^^Seven^^^^Eight^^^^Nine^^^^^Ten^^^^^^Eleven^^^Twelve^^^Thirteen^Fourteen^Fifteen^^Sixteen^^SeventeenEighteen^Nineteen ^"

TenTx Calc (Text) = "^^^^^^^^^^^^^^Twenty^Thirty^Forty^^Fifty^^Sixty^^SeventyEighty^Ninety^"

AllNumberText Calc (Text) = if (HT, trim (middle (TnTx, HT * 9 + 1, 9)) & if (TTh, "^Hundred^", "^Hundred Thousand^"), "") & if (TTh, if (TTh > 19, trim (middle (TenTx, Int (TTh / 10) * 7 + 1, 7)) & "^" & trim (middle (TnTx, mod (TTh, 10) * 9 + 1, 9)), trim (middle (TnTx, TTh * 9 + 1, 9))) & "^Thousand", "") & "^" & if (Hd, trim (middle (TnTx, Hd * 9 + 1, 9)) & "^Hundred^", "") & if (Tn > 19, trim (middle (TenTx, Int (Tn / 10) * 7 + 1, 7)) & "^" & trim (middle (TnTx, mod (Tn, 10) * 9 + 1, 9)), trim (middle (TnTx, Tn * 9 + 1, 9))) & "^and^" & round ((Amt - Int (Amt)) * 100, 0) & "/100"

[REMEMBER: for clarity, a caret (^) is used to indicate when you should type a space.]

HTH.

[djgogi]

quote:

---

Originally posted by Neville:

I know this one is dead easy but...

I want to convert number eg

87200

to Eighty seven thousand and two hundred dollars for printing on documents

I had the code to do it (Apple in those days, about 6 years ago)) faxed it to me but I didn't use it at the time.

I need it now and went to get the fax out of the file

(we all know what happens to thermal faxes - They go blank over years don't they!)

So dont know how to do it

I'n not sure if it was a field or a scrip step

help appreciated.

Neville

---

It's very simillar to Andy's post

code:

---

If(Mod(Int(cash/10^5),10),MiddleWords(upTo20,Mod(Int(cash/10^5),10),1) & " hundred ","") &

If(Mod(Int(cash/10^4),10),

If(Mod(Int(cash/10^4),10) >= 2, MiddleWords(tenths,Mod(Int(cash/10^4),10),1) &

If(Mod(Int(cash/10^3),10),MiddleWords(upTo20,Mod(Int(cash/10^3),10),1) ,""),

If(Mod(Int(cash/10^3),10),MiddleWords(upTo20,Mod(Int(cash/10^3),10)+10,1),"ten")),

If(Mod(Int(cash/10^3),10),MiddleWords(upTo20,Mod(Int(cash/10^3),10),1),""))

& " thousand " &

If(Right(cash, 3), " and ", "") &

If(Mod(Int(cash/10^2),10),MiddleWords(upTo20,Mod(Int(cash/10^2),10),1) & " hundred ","") &

If(Mod(Int(cash/10),10),

If(Mod(Int(cash/10),10) >= 2, MiddleWords(tenths,Mod(Int(cash/10),10),1) &

If(Mod(cash,10),MiddleWords(upTo20,Mod(cash,10),1) ,""),

If(Mod(cash,10),MiddleWords(upTo20,Mod(cash,10)+10,1),"ten")),

If(Mod(cash,10),MiddleWords(upTo20,Mod(cash,10),1),""))

& " dolars"

where upTo20 is global text field with value:

one two three four five six seven eight nine ten eleven twelwe thirteen fourtheen fiftheen sixtheen seventheen eightheen nintheen

and tenths is global text field with value:

ten twenty thirty fourthy fifthy sixty seventy eigthy ninety

and cash is obviously your number

Check my spellings

HTH

Dj

[Vaughan]

The solution becomes *much* easier if you can express the number in more simple terms rather than get it to describe the number.

I mean "eight seven two zero zero" instead of "eighty seven thousand two hundred."

[Neville]

Hi Andy Gaunt

Thanks for your help

I have it almost right but...

Somthing is slightly wrong eg everything ends with "and 0/100"

such as 170,000 converts to

One Hundred Seventy Thousand and 0/100

Looking at your example the calc ends with "and 0/100" so don't see how it will do anything else.

The code is above me so it is a bit of "leap of faith" as I don't understand how it works.

Am I doing somthing wrong??

I copied and pasted the code directly so I wouldn't make a typo and changed the carrots to spaces carefully.

The code in the cale is (copy & paste from FM)

If(HT , Trim(Middle(TnTx , HT * 9 + 1, 9)) & If(TTh , " Hundred ", " Hundred Thousand "), "") & If(TTh , If(TTh > 19, Trim(Middle(TenTx, Int(TTh / 10) * 7 + 1, 7)) & " " & Trim(Middle(TnTx , Mod(TTh , 10) * 9 + 1, 9)), Trim(Middle(TnTx , TTh * 9 + 1, 9))) & " Thousand", "") & " " & If(Hd , Trim(Middle(TnTx , Hd * 9 + 1, 9)) & " Hundred ", "") & If(Tn > 19, Trim(Middle(TenTx, Int(Tn / 10) * 7 + 1, 7)) & " " & Trim(Middle(TnTx , Mod(Tn , 10) * 9 + 1, 9)), Trim(Middle(TnTx , Tn * 9 + 1, 9))) & " and " & Round((Amt - Int(Amt)) * 100, 0) & "/100"

The code seems to assume that there will always be some number after the thousands but this is not always the case. eg Is the code smart enough such that if there is trailing zeros (ex X,000 or xx,000, or xxx,000 it will "quit" after the thousands and not put in "and"

NEARLY THERE!

Thanks in anticipation

[BobWeaver]

That's the part that should display the cents. For example 170356.45 should display as "one hundred seventy thousand three hundred fifty-six and 45/100 dollars" Since the last part should display leading zeroes, the last part of Andy's formula should be changed from:

round ((Amt - Int (Amt)) * 100, 0) & "/100"

to:

Right("00"&round ((Amt - Int (Amt)) * 100, 0),2) & "/100"