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

12 Replies

Sort By
    • 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
        4 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
        ------------------------------
  • 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? 
    • HeatherBryant2's avatar
      HeatherBryant2
      Qrew Assistant Captain
      5 years ago
      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? 
    • 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")
  • 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