Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

Converting Numeric Amount to Written Out Text?

Stefan Huyghe

By Stefan Huyghe
in The Left Brain

 Share

This topic is 7343 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Stefan Huyghe Rookie

Stefan Huyghe

Posted
  • Newbies
Posted

I am making a check template. When the accounting department prints out checks this template needs to post the amount both in Numerical and Text formats. To my knowledge Filemaker does not have a function that does this. Does anyone have a solution. In essence if the amount is $1245.56 I want a calculated field to display "One thousand two hundred and fourty five dollars and fifty six cents". Can anybody point me in the right direction?

Thanks for your help!

John Glenn Rookie

John Glenn

Posted
Posted

I feel like I saw a discussion about that somewhere recently, may be a custom FileMaker 7 function or something, I can't remember.

I got this from somewhere years ago, I can't remember where, and I make absolutely no guarantees that it doesn't have bugs or whatever. but it seems to work for me.

( (If( (Length(Int(Check amount))=1);

(Choose(Middle(GetAsNumber(Check amount); 1; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine "));

(If( (Length(Int(Check amount))=2);

((Choose(Middle(GetAsNumber(Check amount); 1; 1);

""; (Choose(Middle(GetAsNumber(Check amount); 2; 1);

"Ten"; "Eleven" ; "Twelve"; "Thirteen"; "Fourteen"; "Fifteen"; "Sixteen";

"Seventeen"; "Eighteen"; "Nineteen"));

"Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety")) &

(If((Middle(GetAsNumber(Check amount); 2; 1) > 0) and (Middle(GetAsNumber(Check amount); 1; 1) ? 1);

"-";

" ")) &

(If((Middle(GetAsNumber(Check amount); 1; 1) ? 1);

(Choose(Middle(GetAsNumber(Check amount); 2; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine "));

"")));

(If( (Length(Int(Check amount))=3);

((Choose(Middle(GetAsNumber(Check amount); 1; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine ")) & "Hundred " &

(Choose(Middle(GetAsNumber(Check amount); 2; 1);

""; (Choose(Middle(GetAsNumber(Check amount); 3; 1);

"Ten "; "Eleven " ; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen ";

"Seventeen "; "Eighteen "; "Nineteen "));

"Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety")) &

(If((Middle(GetAsNumber(Check amount); 3; 1) > 0) and (Middle(GetAsNumber(Check amount); 2; 1) > 1);

"-";

(If((Middle(GetAsNumber(Check amount); 2; 1) > 1); " "; "")))) &

(If((Middle(GetAsNumber(Check amount); 2; 1) ? 1);

(Choose(Middle(GetAsNumber(Check amount); 3; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine "));

"")));

(If( (Length(Int(Check amount))=4);

((Choose(Middle(GetAsNumber(Check amount); 1; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine ")) & "Thousand" & (If((Middle(GetAsNumber(Check amount); 2; 1) = "0");

" "; "")) &

(Choose(Middle(GetAsNumber(Check amount); 2; 1);

""; ", One "; ", Two "; ", Three "; ", Four "; ", Five "; ", Six ";

", Seven "; ", Eight "; ", Nine ")) &

(If((Middle(GetAsNumber(Check amount); 2; 1) ? 0);

"Hundred " ;

"")) &

(Choose(Middle(GetAsNumber(Check amount); 3; 1);

""; (Choose(Middle(GetAsNumber(Check amount); 4; 1);

"Ten "; "Eleven " ; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen ";

"Seventeen "; "Eighteen "; "Nineteen "));

"Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety")) &

(If((Middle(GetAsNumber(Check amount); 4; 1) > 0) and (Middle(GetAsNumber(Check amount); 3; 1) > 1);

"-";

(If((Middle(GetAsNumber(Check amount); 3; 1) > 1); " "; "")))) &

(If((Middle(GetAsNumber(Check amount); 3; 1) ? 1);

(Choose(Middle(GetAsNumber(Check amount); 4; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine "));

"")));

(If( (Length(Int(Check amount))=5);

((Choose(Middle(GetAsNumber(Check amount); 1; 1);

""; (Choose(Middle(GetAsNumber(Check amount); 2; 1);

"Ten "; "Eleven " ; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen ";

"Seventeen "; "Eighteen "; "Nineteen "));

"Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety")) &

(If((Middle(GetAsNumber(Check amount); 2; 1) > 0) and (Middle(GetAsNumber(Check amount); 1; 1) > 1);

"-";

(If((Middle(GetAsNumber(Check amount); 1; 1) > 1); " "; "")))) &

(If((Middle(GetAsNumber(Check amount); 1; 1) ? 1);

(Choose(Middle(GetAsNumber(Check amount); 2; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine "));

"")) & "Thousand" & (If((Middle(GetAsNumber(Check amount); 3; 1) = "0");

" "; "")) &

(Choose(Middle(GetAsNumber(Check amount); 3; 1);

""; ", One "; ", Two "; ", Three "; ", Four "; ", Five "; ", Six ";

", Seven "; ", Eight "; ", Nine ")) &

(If((Middle(GetAsNumber(Check amount); 3; 1) ? 0);

"Hundred ";

"")) &

(Choose(Middle(GetAsNumber(Check amount); 4; 1);

""; (Choose(Middle(GetAsNumber(Check amount); 5; 1);

"Ten "; "Eleven " ; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen ";

"Seventeen "; "Eighteen "; "Nineteen "));

"Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety")) &

(If((Middle(GetAsNumber(Check amount); 5; 1) > 0) and (Middle(GetAsNumber(Check amount); 4; 1) > 1);

"-";

(If((Middle(GetAsNumber(Check amount); 4; 1) > 1); " "; "")))) &

(If((Middle(GetAsNumber(Check amount); 4; 1) ? 1);

(Choose(Middle(GetAsNumber(Check amount); 5; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine "));

"")));

(If( (Length(Int(Check amount))=6);

((Choose(Middle(GetAsNumber(Check amount); 1; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine ")) &

"Hundred " &

(Choose(Middle(GetAsNumber(Check amount); 2; 1);

""; (Choose(Middle(GetAsNumber(Check amount); 3; 1);

"Ten "; "Eleven " ; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen ";

"Seventeen "; "Eighteen "; "Nineteen "));

"Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety")) &

(If((Middle(GetAsNumber(Check amount); 3; 1) > 0) and (Middle(GetAsNumber(Check amount); 2; 1) > 1);

"-";

(If((Middle(GetAsNumber(Check amount); 2; 1) > 1); " "; "")))) &

(If((Middle(GetAsNumber(Check amount); 2; 1) ? 1);

(Choose(Middle(GetAsNumber(Check amount); 3; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine "));

"")) & "Thousand" & (If((Middle(GetAsNumber(Check amount); 4; 1) = "0");

" "; "")) &

(Choose(Middle(GetAsNumber(Check amount); 4; 1);

""; ", One "; ", Two "; ", Three "; ", Four "; ", Five "; ", Six ";

", Seven "; ", Eight "; ", Nine ")) &

(If((Middle(GetAsNumber(Check amount); 4; 1) ? 0);

"Hundred ";

"")) &

(Choose(Middle(GetAsNumber(Check amount); 5; 1);

""; (Choose(Middle(GetAsNumber(Check amount); 6; 1);

"Ten "; "Eleven " ; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen ";

"Seventeen "; "Eighteen "; "Nineteen "));

"Twenty"; "Thirty"; "Forty"; "Fifty"; "Sixty"; "Seventy"; "Eighty"; "Ninety")) &

(If((Middle(GetAsNumber(Check amount); 6; 1) > 0) and (Middle(GetAsNumber(Check amount); 5; 1) > 1);

"-";

(If((Middle(GetAsNumber(Check amount); 5; 1) > 1); " "; "")))) &

(If((Middle(GetAsNumber(Check amount); 5; 1) ? 1);

(Choose(Middle(GetAsNumber(Check amount); 6; 1);

""; "One "; "Two "; "Three "; "Four "; "Five "; "Six ";

"Seven "; "Eight "; "Nine "));

"")));

"INVALID")))))))))))) )

&

If((Int(Check amount) > 0); "and "; "") &

(If( (Length(Round(Mod(Check amount;1); 2) * 100) = 1) and (Round(Mod(Check amount;1);2) ? 0) ;

("0" &(Round(Mod(Check amount;1); 2) * 100));

(If( ((Round(Mod(Check amount;1); 2) * 100) = 0);

"00";

(Round(Mod(Check amount;1); 2) * 100))))) &

"/100"

  • 3 months later...
  • Newbies
Stefan Huyghe Rookie

Stefan Huyghe

Posted
  • Author
  • Newbies
Posted

Dear John,

Thank you for your script.

When I finally got to implement it I noticed that it had about 7 or 8 question marks throughout the text.

I had to guess what those were in the original code and I think I guessed wrong because some of my numbers are not converting. Can you check what the actual character is I should use?

Thanks again for your help.

Best regards,

Stefan

-Queue- Grand Master

-Queue-

Posted
Posted

The question marks were the 'not equal to' sign, which doesn't show up in the HTML. You can use <> instead.

I took the liberty of tweaking the hell out of the posted calculation to work more efficiently with 7.


Case( not IsEmpty(GetAsNumber(CheckAmount));





  Let([ P = Position( CheckAmount; "."; 0; 1 );  

           C = GetAsNumber( Left( CheckAmount; Case( not P; Length(CheckAmount); P ) ));

         D1 = "One Two Three Four Five Six Seven Eight Nine"; 

         D2 = "Eleven Twelve Thirteen Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen";

         D3 = "Ten Twenty Thirty Forty Fifty Sixty Seventy Eighty Ninety";

            L = Length© ];

         

  Case( L > 6; "INVALID"; 



    Substitute( 



      Case( L > 5; MiddleWords( D1; Left( C; 1 ); 1 ) & " Hundred " )  &  



      Case( L > 4; Let([ C = Right( C; 5 ); T = Left( C; 1 ); O = Middle( C; 2; 1 ) ]; 



        Case( not O; Case( T; MiddleWords( D3; T; 1 ) ); T + not T = 1; MiddleWords( Case( T; D2; D1 ); O; 1 ); MiddleWords( D3; T; 1 ) & "-" & MiddleWords( D1; O; 1 ) ) & 

        " Thousand" & Case( Middle( C; 3; 1 ); "," ) & " " ) ) &



      Case( L = 4; MiddleWords( D1; Left( C; 1 ); 1 ) & " Thousand" & Case( Middle( C; 2; 1 ); "," ) & " " ) & 



      Case( L > 2; Let([ C = Right( C; 3 ); T = Left( C; 1 ) ];



        Case( T; MiddleWords( D1; Left( C; 1 ); 1 ) & " Hundred " ) ) ) & 



      Case( L > 1; Let([ C = Right( C; 2 ); T = Left( C; 1 ); O = Right( C; 1 ) ];



        Case( not O; Case( T; MiddleWords( D3; T; 1 ) ); T + not T = 1; MiddleWords( Case( T; D2; D1 ); O; 1 ); MiddleWords( D3; T; 1 ) & "-" & MiddleWords( D1; O; 1 ) ) ) ) & 



      Case( L = 1 and C; MiddleWords( D1; C; 1 ) ) & 



      Case( Int(GetAsNumber(CheckAmount)); " and " ) & Right( "00" & 100 * Round( CheckAmount; 2 ); 2 ) & "/100"; "  "; " " 



    )



  )

  )

)

Enjoy wink.gif

-Queue- Grand Master

-Queue-

Posted
Posted

You're welcome, Stefan. It made for a nice, distracting challenge to finish out the week. wink.gif

This topic is 7343 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.

  I accept