Forum Discussion

LiaKatz's avatar
LiaKatz
Qrew Trainee
7 years ago

Number to Text field - maintaining commas

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? 
Ask about Quickbase functionality
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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's avatar
    LiaKatz
    Qrew Trainee
    7 years ago
    thanks, but its not working. I assume I change out my numeric field for the 'value' in the formula? 
    • LiaKatz's avatar
      LiaKatz
      Qrew Trainee
      7 years ago
      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? 
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      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")
    • LienGilhooley's avatar
      LienGilhooley
      Qrew Cadet
      5 years 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
      ------------------------------
      • BramTyler1's avatar
        BramTyler1
        Qrew Member
        5 years ago
        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
        ------------------------------