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?

Chris, good catch. I have put in a support ticket on this.------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

I have a few formuals, but try this onevar 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

thanks, but its not working. I assume I change out my numeric field for the 'value' in the formula?

Sorry, I should have explanied.Change where I have [Open Amount] to your numeric fieldThis formula makes use of formula variables.https://help.quickbase.com/user-assistance/formula_variables.html

Number to Text field - maintaining commas

DanLadner1

Qrew Trainee

7 years ago

There's also a built-in function: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=188

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

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
------------------------------

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?
- QuickBaseCoachD
  Qrew Captain
  6 years ago
  Can you clarify which formula you are using?
- HeatherBryant2
  Qrew Assistant Captain
  6 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
  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
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

Forum Discussion

Number to Text field - maintaining commas