Jump to content

Convert Number to Text


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

Recommended Posts

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";"")

post-109827-0-74703800-1416520899_thumb.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 ) 
Link to comment
Share on other sites

This topic is 2922 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

×
×
  • Create New...

Important Information

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