Discussions

Expand all | Collapse all

Number to Text field - maintaining commas

  • 1.  Number to Text field - maintaining commas

    Posted 01-26-2018 19:56
    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? 


  • 2.  RE: Number to Text field - maintaining commas

    Posted 01-26-2018 20:01
    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


  • 3.  RE: Number to Text field - maintaining commas

    Posted 01-26-2018 20:15
    thanks, but its not working. I assume I change out my numeric field for the 'value' in the formula? 


  • 4.  RE: Number to Text field - maintaining commas

    Posted 01-26-2018 20:18
    Sorry, I should have explanied.

    Change where I have [Open Amount] to your numeric field

    This formula makes use of formula variables.

    https://help.quickbase.com/user-assistance/formula_variables.html


  • 5.  RE: Number to Text field - maintaining commas

    Posted 01-26-2018 20:27
    wow, that's awesome. thank you! if I want to add millions in there, what do I do? and how do I round down to 1 decimal point? 


  • 6.  RE: Number to Text field - maintaining commas

    Posted 01-26-2018 20:53
    This is a another formula I have, but for some reason it drops the decimal after the number gets very large. It will also  not deal with negative numbers.

    var number MyNumber = Round([Big Number],0.1); 


    var number amt = ToNumber(Part(ToText($MyNumber), 1, "."));
     
    var text dec = Part(ToText($MyNumber), 2, ".");
     

    (   If($amt < 1000,
     ToText($amt), 
       $amt < 1000000, 
        NotRight(ToText($amt), 3) & "," & 
        Right(ToText($amt), 3), 
        $amt < 10000000,
        NotRight(ToText($amt), 6) & "," &  
        Mid(ToText($amt), 2, 3) & "," & 
        Right(ToText($amt), 3),
        $amt < 100000000, 
        NotRight(ToText($amt), 6) & "," & Mid(ToText($amt), 3, 3) & "," & Right(ToText($amt), 3),    ""))&
        If($dec <> "","." & If(Length($dec)<1,$dec & "0", $dec),"") 


    The alternative is to just use the build in function as Dan suggested



    ToFormattedText([My number field], "comma_dot")


  • 7.  RE: Number to Text field - maintaining commas

    Posted 06-28-2019 17:37
    Hey Mark...I am using this formula and it is working quite nicely except that it is only displaying one decimal point, and I'd like for it to display two. 

    Do you have a tip on how to do that? 


  • 8.  RE: Number to Text field - maintaining commas

    Posted 06-28-2019 18:05
    Can you clarify which formula you are using?


  • 9.  RE: Number to Text field - maintaining commas

    Posted 01-26-2018 20:20
    There's also a built-in function: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=188