# Number to Text field - maintaining commas

• 0
• Question
• Updated 1 year ago
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?
• 90 Points

Posted 1 year ago

• 0
• 70,504 Points
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
• 90 Points
thanks, but its not working. I assume I change out my numeric field for the 'value' in the formula?
• 70,204 Points
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
• 90 Points
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?
• 70,204 Points
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")
• 1,558 Points
There's also a built-in function: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=188