FM - Convert Currency Number to Currency Text

rvfowler2
rvfowler2 used Ask the Experts™
on
For a Lease Document, I am looking for a calculation that converts a currency number to currency text, e.g., $2,250.00 or Two Thousand Two Hundred Fifty And 00/100 Dollars/Mo.  Searched EE and found nothing and Googled and found similar calcs.  wondering what the best approach would be.  I could write it, but am afraid it wouldn't be the most efficient, so wondering what the best approach would be.  I'm sure a Let statement with variables and using a Right function to count places from the right to capture the correct number and then using a Case Statement to equate the number with a particular text.  Am I on the right track?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President, Dedication Technologies, Inc.
Commented:
The simplest way to do this is to use a Custom Function of which there are many. Go to:

http://www.briandunning.com/filemaker-custom-functions/ 

and search on "Currency". You'll find a number of Custom Functions for converting a number to text. FileMaker Advanced is required for installing Custom Functions. If you don't have FileMaker Advanced you can still use the Custom Function calculation in a Calculation field directly.

Here is one example: http://www.briandunning.com/cf/514

Another is here: http://www.briandunning.com/cf/106

The latter one is called:

NumberInWords ( NumberIn; Currency; DecimalCurr )

Sample Input: 61915.21
Sample Output: Sixty One Thousand Nine Hundred Fifteen Dollars and Twenty One Cents

and just to give an idea of what the calculation might look like, here's the actual Custom Function calculation using the NumberIn, Currency and DecimalCurr input variables as noted above. Give a null value for Currency if you don't want one. If you want the decimal part to be ignored, set Decimalcurr to "None".

Case(NumberIn = 0; "";

Let ( [
FullNumber = GetAsNumber( numberin );
Number = Int( FullNumber );
len = If ( Number = 0 ; 0 ; Int(Log(Number)) + 1 )
] ;
Case(len > 15;
  "Out of Range";
Currency = "EvalOnly";
   Let([
    Hundred = Left(number; 1);
    TensOnes = Right(number; 2);
    TONum = GetAsNumber(TensOnes);
    Tens = Left(TensOnes; 1);
    Ones = If( TONum < 20; TensOnes; Right(TensOnes; 1))
  ];
  Case(len = 3;
    Choose(hundred; ""; "One"; "Two"; "Three"; "Four "; "Five"; "Six"; "Seven"; "Eight"; "Nine") & " Hundred" & Case(TONum¿0;" "))
  & Case(TONum > 19;
    Choose(tens; ""; ""; "Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety") & Case(Ones ¿ 0; " "))
  & Choose(ones; ""; "One"; "Two"; "Three"; "Four "; "Five"; "Six"; "Seven"; "Eight"; "Nine"; "Ten"; "Eleven"; "Twelve"; "Thirteen"; "Fourteen"; "Fifteen"; "Sixteen"; "Seventeen"; "Eighteen"; "Nineteen")
);

  Case(len >3;
    Let([
      UseLeft = Choose(Mod(len;  3);3; 1; 2);
      NewLen= 10 ^ (len - UseLeft);
      ThisValue = Div(number; NewLen);
      NewValue = Number - ThisValue * NewLen
    ];
    NumberInWords ( ThisValue; "EvalOnly"; "None" ) & " " & Choose( Div(len - 1; 3); ""; "Thousand"; "Million"; "Billion"; "Trillion" ) & Case(NewValue ¿ 0;" " & NumberInWords(NewValue; ""; "None"))
    );
  len = 0; "Zero" ;
  NumberInWords(Number; "EvalOnly"; "None")) & Case(currency ¿ ""; " " & Currency)
  &  Case(DecimalCurr ¿ "None"; " and " 
    & Let(
      Decimal = Int(100 * (fullnumber - number));
    If(Decimal = 0; "Zero"; NumberInWords(Decimal; "EvalOnly"; "None"))) & " " & DecimalCurr))
))
Thanks, one didn't work, but this one did.  

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

Author

Commented:
Just adding clarification.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial