Salesforce and other SMB Solutions are coming soon. ×

# converting number to text ## Recommended Posts  Hi Guys

I have to write to a report my grand total example \$1500.00 in text form. Like "One thousand five hundred" is there a way of doing this.

thanks

Mantral

##### Share on other sites  There's a nice custom function for that floating around; I see you do not have Filemaker Advanced so you can't used CFs yourself, but I'll see if I can find it and, if it's not recursive, you could use the same code in a plain-vanilla calc field.

Found it, on Brian Dunning's site. And although it's long and cumbersome-looking, it's not recursive so you can use it as an ordinary field definitionl

http://www.briandunning.com/cf/514

Replace the word "NumberField" with your actual field name, other than that just paste that whole thing in as your field definition. Calc field, result type = "text".

##### Share on other sites  Thanks for the instantaneous reply Ahunter.

But I do have the advanced version pro 9. My program is advance but my brain is not.smiley-smile

thanks again

Mantral

##### Share on other sites  In that case you've got a choice, and either way should work.

##### Share on other sites  Hi Guys,

Three ThousandOne Hundred Dollars this is what I get in one of my test with the moneytowords calculation.

Please advise where I can find the part of the calculation that could put a space between the word Thousand and One.

thanks

##### Share on other sites  Odd.

The exact number you input? 3100?

The fx does indeed appear to be a bit buggy, I get this:

Three Thousand, One Hundred and Dollars

I'm inclined to dive in and see if I can improve it a bit but I'd like to know what number you input that resulted in the run-together 'ThousandOne' ??

##### Share on other sites  Hi Ahunter3;

The exact number is 3100.

thanks

##### Share on other sites  I edited the formula, the original author had

& Case(Int(Mod(NumberField;10^3) / 100)>0 and Int(NumberField / 10)>0;"and ";"")

down towards the bottom, the last "and" prior to dealing with "dollars" or "cents"

I changed it to

& Case(Int(Mod(NumberField;10^3) / 100)>0 and Int(Mod(NumberField;10))>0 ;"and ";"")

and I no longer get "Three Thousand One Hundred and Dollars"

I do not, however, get your absence of a space. Don't know why you're getting that.

Here's the final formula I ended up with, it doesn't seem to break:

```Choose(Int(Mod(NumberField;10^12) / 10^11); ""; "One Hundred "; "Two
Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six
Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ")
& Case(Int(Mod(NumberField;10^12) / 10^11)and Int(Mod(NumberField;10^10) / 10^9)>0;"and ";"")
&
If(Int(Mod(NumberField;10^11) / 10^10) = 1;
Choose(Int(Mod(NumberField;10^10) / 10^9); "Ten "; "Eleven "; "Twelve
"; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Int(Mod(NumberField;10^11) / 10^10); ""; ""; "Twenty ";
"Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty ";
"Ninety ")
&
Choose(Int(Mod(NumberField;10^10) / 10^9); ""; "One "; "Two "; "Three
"; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine "))
&
Case(Int(Mod(NumberField;10^12)/10^9) and Int(Mod(NumberField;10^9) /
10^3); "Billion, "; Int(Mod(NumberField;10^12)/10^9);"Billion ";"")
&
Choose(Int(Mod(NumberField;10^9) / 10^8); ""; "One Hundred "; "Two
Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six
Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ")
//
& Case(Int(Mod(NumberField;10^9) / 10^8)>0 and Int(Mod(NumberField;10^8) / 10^7)>0;"and ";"")
//
&
If(Int(Mod(NumberField;10^8) / 10^7) = 1;
Choose(Int(Mod(NumberField;10^7) / 10^6); "Ten "; "Eleven "; "Twelve
"; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Int(Mod(NumberField;10^8) / 10^7); ""; ""; "Twenty "; "Thirty
"; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ")
&
Choose(Int(Mod(NumberField;10^7) / 10^6); ""; "One "; "Two "; "Three
"; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine "))
&
Case(Int(Mod(NumberField;10^9)/10^6) and Int(Mod(NumberField;10^6) /
10^3); "Million, "; Int(Mod(NumberField;10^9)/10^6);"Million ";"")
&
Choose(Int(Mod(NumberField;10^6) / 10^5); ""; "One Hundred "; "Two
Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six
Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ")
//
& Case(Int(Mod(NumberField;10^6) / 10^5)>0 and Int(Mod(NumberField;10^5) / 10^4)>0;"and ";"")
//
&
If(Int(Mod(NumberField;10^5) / 10^4) = 1;
Choose(Int(Mod(NumberField;10^4) / 10^3); "Ten "; "Eleven "; "Twelve
"; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Int(Mod(NumberField;10^5) / 10^4); ""; ""; "Twenty "; "Thirty
"; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ")
&
Choose(Int(Mod(NumberField;10^4) / 10^3); ""; "One "; "Two "; "Three
"; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine "))
&
//
Case(Int(Mod(NumberField;10^6)/10^3) and Int(Mod(NumberField;10^3) /10^0); "Thousand" & Case(Int(Mod(NumberField;10^3) / 100)=0;" and ";", "); Int(Mod(NumberField;10^6)/10^3);"Thousand ";"")
//
&
Choose(Int(Mod(NumberField;10^3) / 100); ""; "One Hundred "; "Two
Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six
Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ")

& Case(Int(Mod(NumberField;10^3) / 100)>0 and Int(Mod(NumberField;10))>0 ;"and ";"")

&
If(Int(Mod(NumberField;100) / 10) = 1;
Choose(Int(Mod(NumberField;10)); "Ten "; "Eleven "; "Twelve ";
"Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Int(Mod(NumberField;100) / 10); ""; ""; "Twenty "; "Thirty ";
"Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose(Int(Mod(NumberField;10)); ""; "One "; "Two "; "Three "; "Four
"; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) &
Case(Int(NumberField) > 1; "Dollars"; Int(NumberField) = 1; "Dollar";
"")
&
If(Mod(NumberField; 1) and Int(NumberField);" and ";"")
&
If(Mod(Int(NumberField*10); 10) = 1;
Choose(Mod(Int(NumberField*100); 10); "Ten "; "Eleven "; "Twelve ";
"Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Mod(Int(NumberField*10); 10); ""; ""; "Twenty "; "Thirty ";
"Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ")
&
Choose(Mod(Int(NumberField*100); 10); ""; "One "; "Two "; "Three ";
"Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) &
Case(Mod(NumberField; 1) > .01; "cents"; Mod(NumberField; 1) = .01; "cent";"")
```

##### Share on other sites  Hi Ahunter3,

Got it and will try it this time. Looks promising...

I do not know how it works or the mechanics of the codes but it is doing it's job. You are just overwhelmed by the size of it but it works.

Thanks a lot for your time and patient's.

Mantral

##### Share on other sites  AHunter3

I'm lost as to where you add this information to convert number to text? I've been using FMP for years with little knowledge of it super powers. Can you help me?

I edited the formula, the original author had

down towards the bottom, the last "and" prior to dealing with "dollars" or "cents"

I changed it to

and I no longer get "Three Thousand One Hundred and Dollars"

I do not, however, get your absence of a space. Don't know why you're getting that.

Here's the final formula I ended up with, it doesn't seem to break:

```Choose(Int(Mod(NumberField;10^12) / 10^11); ""; "One Hundred "; "Two
Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six
Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ")
& Case(Int(Mod(NumberField;10^12) / 10^11)and Int(Mod(NumberField;10^10) / 10^9)>0;"and ";"")
&
If(Int(Mod(NumberField;10^11) / 10^10) = 1;
Choose(Int(Mod(NumberField;10^10) / 10^9); "Ten "; "Eleven "; "Twelve
"; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Int(Mod(NumberField;10^11) / 10^10); ""; ""; "Twenty ";
"Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty ";
"Ninety ")
&
Choose(Int(Mod(NumberField;10^10) / 10^9); ""; "One "; "Two "; "Three
"; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine "))
&
Case(Int(Mod(NumberField;10^12)/10^9) and Int(Mod(NumberField;10^9) /
10^3); "Billion, "; Int(Mod(NumberField;10^12)/10^9);"Billion ";"")
&
Choose(Int(Mod(NumberField;10^9) / 10^8); ""; "One Hundred "; "Two
Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six
Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ")
//
& Case(Int(Mod(NumberField;10^9) / 10^8)>0 and Int(Mod(NumberField;10^8) / 10^7)>0;"and ";"")
//
&
If(Int(Mod(NumberField;10^8) / 10^7) = 1;
Choose(Int(Mod(NumberField;10^7) / 10^6); "Ten "; "Eleven "; "Twelve
"; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Int(Mod(NumberField;10^8) / 10^7); ""; ""; "Twenty "; "Thirty
"; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ")
&
Choose(Int(Mod(NumberField;10^7) / 10^6); ""; "One "; "Two "; "Three
"; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine "))
&
Case(Int(Mod(NumberField;10^9)/10^6) and Int(Mod(NumberField;10^6) /
10^3); "Million, "; Int(Mod(NumberField;10^9)/10^6);"Million ";"")
&
Choose(Int(Mod(NumberField;10^6) / 10^5); ""; "One Hundred "; "Two
Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six
Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ")
//
& Case(Int(Mod(NumberField;10^6) / 10^5)>0 and Int(Mod(NumberField;10^5) / 10^4)>0;"and ";"")
//
&
If(Int(Mod(NumberField;10^5) / 10^4) = 1;
Choose(Int(Mod(NumberField;10^4) / 10^3); "Ten "; "Eleven "; "Twelve
"; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Int(Mod(NumberField;10^5) / 10^4); ""; ""; "Twenty "; "Thirty
"; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ")
&
Choose(Int(Mod(NumberField;10^4) / 10^3); ""; "One "; "Two "; "Three
"; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine "))
&
//
Case(Int(Mod(NumberField;10^6)/10^3) and Int(Mod(NumberField;10^3) /10^0); "Thousand" & Case(Int(Mod(NumberField;10^3) / 100)=0;" and ";", "); Int(Mod(NumberField;10^6)/10^3);"Thousand ";"")
//
&
Choose(Int(Mod(NumberField;10^3) / 100); ""; "One Hundred "; "Two
Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six
Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ")

& Case(Int(Mod(NumberField;10^3) / 100)>0 and Int(Mod(NumberField;10))>0 ;"and ";"")

&
If(Int(Mod(NumberField;100) / 10) = 1;
Choose(Int(Mod(NumberField;10)); "Ten "; "Eleven "; "Twelve ";
"Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Int(Mod(NumberField;100) / 10); ""; ""; "Twenty "; "Thirty ";
"Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose(Int(Mod(NumberField;10)); ""; "One "; "Two "; "Three "; "Four
"; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) &
Case(Int(NumberField) > 1; "Dollars"; Int(NumberField) = 1; "Dollar";
"")
&
If(Mod(NumberField; 1) and Int(NumberField);" and ";"")
&
If(Mod(Int(NumberField*10); 10) = 1;
Choose(Mod(Int(NumberField*100); 10); "Ten "; "Eleven "; "Twelve ";
"Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen ";
"Eighteen "; "Nineteen ");
Choose(Mod(Int(NumberField*10); 10); ""; ""; "Twenty "; "Thirty ";
"Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ")
&
Choose(Mod(Int(NumberField*100); 10); ""; "One "; "Two "; "Three ";
"Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) &
Case(Mod(NumberField; 1) > .01; "cents"; Mod(NumberField; 1) = .01; "cent";"")
```

##### Share on other sites  a) Copy the above formula to your clipboard

b) Assuming that your number field in your database has a name different from "Numberfield", paste the above formula into a text editor or a word processor that has a Find All/Replace function; and replace all occurences of NumberField with the appropriate field name.

c) Go into Define Fields. Create a calculation field. Result type needs to be text (set that down at the bottom under the formula area. Pase from clipboard to insert the formula. Click "OK".

OR, alternative, if you wish to use it as a custom function,

Open Define Custom Functions. Click the button to create a new one. You'll have two places you can enter info, aside from where you enter the custom function's name. Start at the top, which is where you enter the named parameters. Type the string "NumberField" and click the "+" to add it to the list of parameters.

Then paste the formula into the big formula area. That's it, you've inserted it as a custom function.

The original author named this function "MoneyToWords". Assuming you keep that as the custom function's name, you would deploy it anywhere in this file that you need it, like so

MoneyToWords(AnyFieldName)

or for that matter

MoneyToWords (Invoice::Subtotal + Invoice::AdjustedTax - Invoice::Discount)

##### Share on other sites  The last line should be:

Case(Mod(CHECK Amount; 1) > .01; "Cents"; Mod(CHECK Amount; 1) = .01;

"Cent";" and Zero Cents")

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.5k
×
×
• Create New...