Is it possible to convert the numeric field in words currency?

  • 1
  • 2
  • Question
  • Updated 2 years ago
  • Answered

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.

Photo of rahul

rahul

  • 50 Points

Posted 3 years ago

  • 1
  • 2
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
Photo of rahul

rahul

  • 50 Points
Thanks Coach, it saved my life :)
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.
Photo of rahul

rahul

  • 50 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
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://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.
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.