Convert Number to Text

[charisse]

I have created a ledger with a layout for writing checks. I would like to have a calculation that would take the number entered for the dollar amount and convert it to text so that I would not have to type in the text for the number every time  I write a check. I found the calculation below on the internet, but I can not get it to work. I am getting the error message in the screenshot when I try to save the calculation. I have no idea where I have gone wrong. Any help would be appreciated. Thanks!

 

Choose(Int(Mod(Debit Column;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(Debit Column;10^11) / 10^10) = 1;
Choose(Int(Mod(Debit Column;10^10) / 10^9); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen ");
Choose(Int(Mod(Debit Column;10^11) / 10^10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose(Int(Mod(Debit Column;10^10) / 10^9); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) &
Case(Int(Mod(Debit Column;10^12)/10^9) and Int(Mod(Debit Column;10^9) / 10^3); "Billion; "; Int(Mod(Debit Column;10^12)/10^9);"Billion ";"")
&
Choose(Int(Mod(Debit Column;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(Debit Column;10^8) / 10^7) = 1;
Choose(Int(Mod(Debit Column;10^7) / 10^6); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen ");
Choose(Int(Mod(Debit Column;10^8) / 10^7); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose(Int(Mod(Debit Column;10^7) / 10^6); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) &
Case(Int(Mod(Debit Column;10^9)/10^6) and Int(Mod(Debit Column;10^6) / 10^3); "Million; "; Int(Mod(Debit Column;10^9)/10^6);"Million ";"")
&
Choose(Int(Mod(Debit Column;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(Debit Column;10^5) / 10^4) = 1;
Choose(Int(Mod(Debit Column;10^4) / 10^3); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen ");
Choose(Int(Mod(Debit Column;10^5) / 10^4); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose(Int(Mod(Debit Column;10^4) / 10^3); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) &
Case(Int(Mod(Debit Column;10^6)/10^3) and Int(Mod(Debit Column;10^3) / 10^0); "Thousand; "; Int(Mod(Debit Column;10^6)/10^3);"Thousand ";"")
&
Choose(Int(Mod(Debit Column;10^3) / 100); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ") &
If(Int(Mod(Debit Column;100) / 10) = 1;
Choose(Int(Mod(Debit Column;10)); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen ");
Choose(Int(Mod(Debit Column;100) / 10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose(Int(Mod(Debit Column;10)); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Int(Debit Column) > 1; "Dollars"; Int(Debit Column) = 1; "Dollar"; "")
&
If(Mod(Debit Column; 1) and Int(Debit Column);" and ";"") &
If(Mod(Int(Debit Column*10); 10) = 1;
Choose(Mod(Int(Debit Column*100); 10); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen ");
Choose(Mod(Int(Debit Column*10); 10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") &
Choose(Mod(Int(Debit Column*100); 10); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) &
Case(Mod(Debit Column; 1) > .01; "cents"; Mod(Debit Column; 1) = .01; "cent";"")

[Chuck]

I would try pasting the calc again. I think you're using the one found at https://www.briandunning.com/cf/19, and I pasted it into a calc field (after creating NumberField) and received no error in FileMaker Pro 13 Advanced.

 

One tip that might help: Before creating the calc, change the "Debit Column" field to "NumberField", which is what the version linked to above references. Afterwards you can change it back and the calc will update. That way you don't have to do a search and replace in a text editor, nor change the field name manually.

[Lee Smith]

Hi Chuck,

 

Where do they say that they have FileMaker Pro Advance?

 

I was going to suggest these CFs earlier, but they seem to be recursive.

 

Lee

[Lee Smith]

Here is one that works that I found at FileMaker LINK

[comment]

@Lee The two (or three) are identical.

[comment]

I found the calculation below on the internet

 

There's no reason why it shouldn't work, but you could have done better by doing the search here:

http://fmforums.com/forum/topic/23991-number-to-words-calc-100-one-hundred/?p=134935

[charisse]

Is that what the problem is, this is a custom function that I don't have access to in FM pro? I work in a peer to peer situation. We do have one copy of Filemaker 11 Pro Advanced. I guess we were thinking that we could use that for development and then implement the solution in Filemaker Pro. Is that possible?  If so what is the CF that I am trying to call? Thank you.

Here is one that works that I found at FileMaker LINK

This is actually the one that I was working from.

[Lee Smith]

@Lee The two (or three) are identical.

That’s strange, I  tested one of them in a regular field and it didn’t work, so I figured that it was recursive for some reasons. This calculation has been around since v3, I have a file dated in 2009.

 

Anyway I don’t have a need for this calculation so I’m not going back to work it out.

[Lee Smith]

Is that what the problem is

That is not what comment just said.

 

If you are having trouble with the one I just linked from FileMaker, then you need to examine your setup.

 

Or post a mock up.

[comment]

Is that what the problem is, this is a custom function that I don't have access to in FM pro?

 

No. Have you tried copying and pasting again? You might have issues with copying some hidden characters. If you still can't make it work, copy from the attached file.

NumToWords.fp7.zip

[charisse]

No. Have you tried copying and pasting again? You might have issues with copying some hidden characters. If you still can't make it work, copy from the attached file.

Thank you. I will try that.

[charisse]

There's no reason why it shouldn't work, but you could have done better by doing the search here:

http://fmforums.com/forum/topic/23991-number-to-words-calc-100-one-hundred/?p=134935

Thanks. I decided to go with this calculation:

 

Case( not IsEmpty(GetAsNumber(Debit Column));

  Let([ 

        Amount = Round ( Debit Column ; 2 ) ;

        P = Position( Amount; "."; 0; 1 );

        C = GetAsNumber( Left( Amount; Case( not P; Length(Amount); 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(Amount)); " and " ) & Right( "00" & 100 * ( Amount); 2 ) & "/100"; "  "; " "

    )

  )

  )

)

 

It works great, but I have noticed an incosistency in the employment of the comma. I would prefer to have no commas at all as it it a check not an essay.

 

5056.99

Five Thousand Fifty-Six and 99/100

 

5456.99

Five Thousand, Four Hundred Fifty-Six and 99/100

 

[Rick Whitelaw]

The use of a comma as in the second example is standard usage between thousands and hundreds. In example one there are no hundreds.

[LaRetta]

I must be old-school then because I've always ignored the comma and most of the software I've used also ignores comma (accounting programs).  But then ... I do not know the entire world ...  :-)

 

When in doubt, google:  Item 2) here:  http://www.wikihow.com/Write-a-Check-With-Cents

 

or here (enter a large number like 622424:  http://banking.about.com/library/bl_write_out_numbers.htm

 

However, if you add any cents to it such as .66, and it too violates what is 'norm' for check writing, LOL.  You say potato and I say spuds.  ADDED:  I see they didn't mean for me to put cents into the box.  It says 'Enter a number' and it should have said, "Enter dollars without cents". 

 

I wish the world were based upon some more exacting standards ... it would make our work much easier.

[charisse]

okay, I figured out the comma thing.

 

 

Thanks. I decided to go with this calculation:

 

Case( not IsEmpty(GetAsNumber(Debit Column));

  Let([ 

        Amount = Round ( Debit Column ; 2 ) ;

        P = Position( Amount; "."; 0; 1 );

        C = GetAsNumber( Left( Amount; Case( not P; Length(Amount); 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(Amount)); " and " ) & Right( "00" & 100 * ( Amount); 2 ) & "/100"; "  "; " "

    )

  )

  )

)

 

It works great, but I have noticed an incosistency in the employment of the comma. I would prefer to have no commas at all as it it a check not an essay.

 

5056.99

Five Thousand Fifty-Six and 99/100

 

5456.99

Five Thousand, Four Hundred Fifty-Six and 99/100

 

 

Now I would like to have the hyphenated text display properly,

 

56

Fifty-six

not

Fifty-Six

 

but I have no idea how to accomplish this or if it is even possible in this calculation. Any help would be most appreciated. Thanks!

[comment]

Now I would like to have the hyphenated text display properly,

 

56

Fifty-six

not

Fifty-Six

 

but I have no idea how to accomplish this or if it is even possible in this calculation.

 

Of course it's possible. Find the two places in the calculation where the hyphen is being added and change the subsequent expression to lower case. IOW, instead of:

& "-" & MiddleWords ( D1; O; 1 )

use:

& "-" & Lower ( MiddleWords ( D1; O; 1 ) )

or:

& "-" & MiddleWords ( Lower(D1); O; 1 ) 

[charisse]

Thanks comment!

 

& "-" & MiddleWords ( Lower(D1); O; 1 )

 

worked perfectly.