Discussions

 View Only
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 01-28-2020 17:05
    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
    ------------------------------



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

    Posted 06-08-2020 14:41
    This post is a bit old now but for those who see this and need help still if you want commas to start appearing appear after 3 digits you can use it like ToFormattedText(9000, "comma_dot", 3) => 9,000.

    Here is the description from Quick Base:
    ToFormattedText (Number n, Text f, Number c)

    Description: Returns a Text value containing the formatted print representation of the number n, using the format f, with separators starting after number c digits.  Valid values for c are 3 or 4; if it is 3, separators will be shown starting after 3 digits instead of after 4.

    Example:
    ToFormattedText(1234.56,"comma_dot", 3) returns "4,567.89"
    ToFormattedText(1234.56,"comma_dot", 4) returns "4567.89"
    ToFormattedText(1234567.89,"comma_dot_2",3) returns "12,34,567.89"
    ToFormattedText(1234.56,"dot_comma", 3) returns "4.567,89"
    ToFormattedText(1234.56,"dot_comma", 4) returns "4567,89"
    ToFormattedText(1234567.89,"dot_comma_2",3) returns "12.34.567,89"


    ------------------------------
    Bram Tyler
    ------------------------------



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

    Posted 12-31-2023 10:39

    Quickbase Support, please update your documentation to include the 3rd parameter mentioned above:



    ------------------------------
    Chris Wheatley
    ------------------------------



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

    Posted 12-31-2023 11:09

    Chris, good catch.  I have put in a support ticket on this.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------