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


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

    Posted 20 days ago
    The ToFormattedText function with the "comma_dot" function works well for me when the value of the number I am converting is 10,000 for greater.  However, if my data value if < 10,000, this function leaves out the comma.

    For example:
    ToFormattedText(10000, "comma_dot") == 10,000
    ToFormattedText(9000, "comma_dot") == 9000

    Has anyone else experienced this?

    ------------------------------
    Lien Gilhooley
    ------------------------------