Example.. I have one Numeric field salary [23000]. Now i want to convert the value of this numeric field into text field as "twenty three thousand dollars ".
It would be great help if you can guide me how to do this.
 10 Points
Posted 3 years ago
QuickBaseCoach App Dev./Training, Champion
 62,316 Points
You 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
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
QuickBaseCoach App Dev./Training, Champion
 62,316 Points
Your 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.
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.
 10 Points
Thanks 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
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
QuickBaseCoach App Dev./Training, Champion
 62,316 Points
Sorry, 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://quickbasecommunity.intuit.com/questions/1101663howcanigetthetextofanumberinthewayonewouldspeakitforexample10000onehundreddollars
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.
The original poster was in fact a senior technical QuickBase employee and it was done on this question here,.
https://quickbasecommunity.intuit.com/questions/1101663howcanigetthetextofanumberinthewayonewouldspeakitforexample10000onehundreddollars
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.
QuickBaseCoach App Dev./Training, Champion
 62,316 Points
I 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.
Related Categories

Tables & fields
 7157 Conversations
 170 Followers