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'...
Forum Discussion
thanks, but its not working. I assume I change out my numeric field for the 'value' in the formula?
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")
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")