Decimals as currency
Are there any ways to get a decimal/number formatted into a currency/dollar? I have a case where a column is being calculated and rounded as a decimal to the hundredths (2 decimal places), but if the amount ends up as a whole number (5.00) or ends with a zero (5.50), it drops off the zero(s) in the decimal. Anyone have a way to ensure the zeros dont drop? Also currently using "Tables: Apply formulas to column" as the action.
Best Answer
-
I just found this question and wanted to chime in. The answer that Jeff_146001 presented would work as a Field Formula, but NOT in the context that Carli_126179 presented as part of a "Tables: Apply formulas to column" Action. That said, I'd like to offer up a more canonical way of formatting currency that would also support international currency and numerical formatting.
In a Field Formula, it would be:
result = fields["number-to-use"].toLocaleString("en-US", {style: "currency", currency: "USD"});
or you can also directly insert the field (i.e.):
result = {{number-to-use}}.toLocaleString("en-US", {style: "currency", currency: "USD"});
I haven't tested the Apply formulas but believe it would be:
columns("field-to-insert-into") = columns("number-to-use").toLocaleString("en-US", {style: "currency", currency: "USD"});
1
Answers
-
@Carli_954489 Try this JavaScript formula:
result = "$" + Number(fields["number"]).toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,');
0 -
@Jeff_146001 this left the column blank in the excel output
0 -
The following formula ended up solving this: columns['Column Name'] = parseFloat(columns['Column Name'] || 0).toFixed(2);
2