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

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

##### Share on other sites

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

##### Share on other sites

Here is one that works that I found at FileMaker LINK

##### Share on other sites

@Lee The two (or three) are identical.

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

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

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

##### 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?

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

##### Share on other sites

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.

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

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

##### Share on other sites

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

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

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

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!

##### 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 ) `
##### Share on other sites

Thanks comment!

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

worked perfectly.

##### Share on other sites

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

## Create an account

Register a new account