Converting Numbers to Text Function

[chrisknight55]

FileMaker Pro 11adv Knowledge Base offers the following calculation to convert a number into words, such as used to write checks.

I've followed the instructions, but no luck. If I put the whole calculation in a calculation:text field, error tells me it can't find the function CalculationField.

I then tried to build a Custom Function CalculationField (text result) but it gets hung up on the second comma.

Help please

================================

Converting Numbers to Words or Numbers to Text

Question

How do you convert numbers to words or numbers to text?

Answer

The following calculation can be used to convert the contents of a number field to words. This formula works for numbers up to 1 trillion, and includes text for Dollars and Cents. If you do not want the words Dollars and Cents in your result, you can delete the parts of the calculation that add these words.

NOTE: This solution is not intended for negative numbers such as -$50 as it will not produce the proper result.

In this calculation, the number field is called NumberField. To use this formula:

Copy/paste the calculation into your favorite word processor.

Use the Find/Change or Search/Replace command to search for NumberField and replace with the name of the number field in your database.

Copy the calculation to the clipboard.

Open your FileMaker Pro database and define a new calculation field. Set the calculation result as text.

Paste (command-v [Mac] or control-v [Win]) into the calculation field formula window

Note 1: Most of the text results in quotes (One Hundred , etc.) have a space after the words inside the quotes. Where you see two quotes as the result (), there is no space within the quotes.

Note 2: the Mod functions use the caret symbol (^), which is a shift-6 on the keyboard.

CalculationField (text result) =

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 ) &

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 ) &

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 ) &

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, , 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 ) &

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,)

Edited October 3, 2010 by Guest

[Lee Smith]

Why reinvent the wheel, go to Brian Dunnings Custom Functions Site

Do a search of Numbers to Text.

Lee

[comment]

If I put the whole calculation in a calculation:text field, error tells me it can't find the function CalculationField.

Use only the part AFTER "CalculationField (text result) = ".

See also:

http://fmforums.com/forum/showtopic.php?tid/112852/

[David Jondreau]

You're missing all the quotation marks from the original post.

http://help.filemaker.com/app/answers/detail/a_id/2860/~/converting-numbers-to-words-or-numbers-to-text