Forum Discussion
7 Replies
Sort By
- QuickBaseCoachDQrew CaptainYou can use this formula. This one seems to also do the cents as well.
var text numt = ToText(Floor([Number]));
var text cents = If(Contains(ToText([Number]), "."), Left(Right(ToText([Number]) & "00", "."), 2),"");
var text teensnum = List(" ",
Case(Floor(ToNumber(Right($numt,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),
Case(ToNumber(Right($numt,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""));
var text teens =
If($teensnum<>"" and [Number]>99, "and ", "") &
Case($teensnum,
"Ten One", "Eleven", "Ten Two", "Twelve", "Ten Three", "Thirteen", "Ten Four", "Fourteen", "Ten Five", "Fifteen", "Ten Six", "Sixteen", "Ten Seven", "Seventeen", "Ten Eight", "Eighteen", "Ten Nine", "Nineteen", $teensnum);
var text teensnumcents =List(" ",
Case(Left($cents,1),
"1", "Ten", "2", "Twenty", "3", "Thirty", "4", "Forty", "5", "Fifty", "6", "Sixty", "7", "Seventy", "8", "Eighty", "9", "Ninety", "0", ""),
Case(Right($cents,1),
"1", "One", "2", "Two", "3", "Three", "4", "Four", "5", "Five", "6", "Six", "7", "Seven", "8", "Eight", "9", "Nine", "0", "")
);
var text teenscents =
If($teensnumcents<>"", " and ", "") &
Case($teensnumcents,
"Ten One", "Eleven", "Ten Two", "Twelve", "Ten Three", "Thirteen", "Ten Four", "Fourteen", "Ten Five", "Fifteen", "Ten Six", "Sixteen", "Ten Seven", "Seventeen", "Ten Eight", "Eighteen", "Ten Nine", "Nineteen", $teensnumcents)& If($teensnumcents<>"", " Cents", "");
List(" ",
If([Number]>=1000000,
Case(Floor([Number]/1000000),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine") & " Million","") & If(Mod([Number],1000000)>0 and [Number]>1000000, ",", ""),
If([Number]>=100000,
Case(Floor(ToNumber(Right($numt,6))/100000),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine") & " Hundred","") & If(Mod([Number],100000)>0 and [Number]>100000, " and", "") ,
If([Number]>=10000,
Case(Floor(ToNumber(Right($numt,5))/10000),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),""),
If([Number]>=1000,
Case(Floor(ToNumber(Right($numt,4))/1000),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, "") & " Thousand","") & If(Mod([Number],1000)>0 and [Number]>1000, ",", ""),
If([Number]>=100,
Case(Floor(ToNumber(Right($numt,3))/100),
1, "One Hundred", 2, "Two Hundred", 3, "Three Hundred", 4, "Four Hundred", 5, "Five Hundred", 6, "Six Hundred", 7, "Seven Hundred", 8, "Eight Hundred", 9, "Nine Hundred", 0, ""),""),
$teens)
& " Dollars" & $teenscents - rahulrahul1Qrew TraineeThanks Coach, it saved my life :)
- QuickBaseCoachDQrew CaptainYour Welcome. I'm a pack rat when it comes to these things.
If you want to repay the favour, I suggest that you set a formula variable at the top like
var number MyNumber = [my number field];
Then change all uses of the [Number] to $MyNumber.
Then in future we will just need to make that one change at the top and not need to look though that whole long formula to change [ Number] to the real field name in our apps. - rahulrahul1Qrew TraineeThanks Coach, I had replaced it with Variable but i found one issue with this formula.
Example - Number = 11600 then formula is giving value "Ten One Thousand, Six hundread dollars" ideally it should say "Eleven thousand and Six hundread dollars" ? i am trying to fix it but would be great if you can tell me which variable is causing this issue? thanks - QuickBaseCoachDQrew CaptainSorry, but I would have to invest time to figure that out. Its not a formula I wrote, it's one that was posted to this forum at some time in the past.
The original poster was in fact a senior technical QuickBase employee and it was done on this question here,.
https://quickbase-community.intuit.com/questions/1101663-how-can-i-get-the-text-of-a-number-in-the-way-one-would-speak-it-for-example-100-00-one-hundred-dollars
My suggestion is to go to that question, thank Sam for his post, explain your problem and ask if he has an updated formula for us. - QuickBaseCoachDQrew CaptainI see that you posted a question to Sam. If he does not respond, (he may not have Notifications enabled for the Forum, I'm not sure), you could put in a ticket to Support, reference the link to that Post and ask them to forward the question to Sam. They know who Sam Jones is.