Number to Text field - maintaining commas

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
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? 
Photo of Lia (EOM) Katz

Lia (EOM) Katz

  • 90 Points 75 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
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
Photo of Lia (EOM) Katz

Lia (EOM) Katz

  • 90 Points 75 badge 2x thumb
thanks, but its not working. I assume I change out my numeric field for the 'value' in the formula? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,612 Points 50k badge 2x thumb
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
Photo of Lia (EOM) Katz

Lia (EOM) Katz

  • 90 Points 75 badge 2x thumb
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? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,612 Points 50k badge 2x thumb
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")
Photo of Dan

Dan

  • 1,180 Points 1k badge 2x thumb
There's also a built-in function: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=188