Forum Discussion

rahulrahul1's avatar
rahulrahul1
Qrew Trainee
8 years ago

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

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.

7 Replies

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