Skip to main content
Question

Number to Text field - maintaining commas

  • January 27, 2018
  • 0 replies
  • 614 views
I am trying to convert a number field to a text field, but keep the number formatted with commas. When formatted as a number, it reads: xx,xxx,xxx.xx but the commas don't stick when I use the 'totext' function. Any ideas? 

    I have a few formuals, but try this one

    var number Value = Round([Open Amount],0.01);
    var text Decimals = "." & Right(ToText(Int($Value * 100)),2);

    var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));

    var text Hundreds=Right(ToText(Int($Value)),3);

    var text Words = 
    List(",",$Thousands,$Hundreds) & $Decimals;

    $Words
    • LiaKatz
    • Author
    • Quickbase Alumni
    • January 27, 2018
    thanks, but its not working. I assume I change out my numeric field for the 'value' in the formula? 
    • DanLadner1
    • Quickbase Alumni
    • January 27, 2018
    There's also a built-in function: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=188
    MarkShnierYou
    Forum|alt.badge.img+22
    • MarkShnierYou
    • Quickbase Alumni
    • May 13, 2026

    I think it just works how it works. 

    If you'd like, you can try this formula. I'm giving credit to Laura Thacker on this one. 

     

    Hi everyone,
     
    I stumbled across a post from Mark 5 years ago with a reply by Ursula Llaveria.  I put this into my “Numbers as Text” application (which is EOTI)  to test it because it was a super-simple (length wise) solution to displaying currency values in text format.  Below is the formula.
     
    //Courtesy of Ursula Llaveria
     
    //-- Grab original value / Grab the decimal value
     
    var number OriginalValue = [Number];
    var number theSplit = Frac([Number]);
     
    //--make sure the decimal value has at least two number, if it doesn't, add a 0. This will make sure that someone that just added a .5 displays as .50
     
    var number cents = ToNumber(Left(Right(ToText($theSplit)&"0","."),2));
    var text numt = ToText(Floor($OriginalValue));
     
    //-- check to make sure that if the user did not add decimals, it still displays .00, adds a 0 before any single digits, and is formatted like so: ($xxx.xx) 
     
    "$" & ToFormattedText(ToNumber($numt),"comma_dot",3) & "." & If($cents>0 and $cents<10,"0"&ToText($cents),If($cents>=10,ToText($cents),"00"));
     
    //The above function makes sure that no decimal is rounded up, but stays exactly as it was input in the field.
     
     
    Regards,
     
    Laura Thacker

    Mark Shnier. Your Quickbase Coach
    Terms & ConditionsAccessibility statement