Add a CURRENCY() function for Formula Fields

[Kamille_Parks]

Right now, to format values pulled from a Currency field in a Formula field, users have to use something akin to the following formula to get “$65,756” to display as such. Otherwise, AirTable displays it as “65756”, no commas, no currency symbol:

IF(
  LEN(CONCATENATE({Trip Budget}))<4,
  {Trip Budget},
  IF(
AND(LEN(CONCATENATE({Trip Budget}))>3,LEN(CONCATENATE({Trip Budget}))<7),
LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
IF(
  AND(LEN(CONCATENATE({Trip Budget}))>6,LEN(CONCATENATE({Trip Budget}))<10),
  LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-6)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),6),3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
  IF(
    AND(LEN(CONCATENATE({Trip Budget}))>9,LEN(CONCATENATE({Trip Budget}))<13),
    LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-9)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),9),3)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),6),3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
  )
)
  )
)

This is obviously inconvenient, and it doesn’t even take into account decimals or amounts greater than $999,999,999.

I propose a new formula function that operates something like this:
CURRENCY([number or field reference], [currency symbol]) => CURRENCY(12345.6,"$") = $12,345.60

[Katherine_Duh]

If the output of your formula is going to be a number, you can use the formatting tab in the field customization menu to do this—just switch the Format from Integer to Currency.

If the output of your formula is going to be text rather than a number, then you will not be able to use the formatting tab in this way (this might be the case, if, for example, you’re concatenating the value from a currency field with a value from a text field). However, if you’re just performing mathematical operations on a value from the currency field, this shouldn’t be an issue.

[Kamille_Parks]

That only works if there the formula presents the number by itself. It doesnt help for formulas that concatenate plain text along with whatevery currency field is being referenced.

A CURRENCY() function is still requested.

[Kamille_Parks]

@Katherine_Duh if you’d like a use case, here’s a shot from my table that sorely needs such a function:

To keep my table a little cleaner/legible, I combined 5 Currency fields into one column using a formula in the {Budget Summary} field. However, this means the formula needs to include the 774 character snippet (which in reality would be longer to handle decimals) featured above for each number.

The CURRENCY() function could operate just like DATETIME_FORMAT() does and would only make formulas less cluttered

[Elias_Gomez_Sai]

What is your Base structure? It seems to me like you could handle that just with Grouping and summary bars.

[Kamille_Parks]

The base already has groups as summary bars implemented and they don’t do whatthat formula is doing. What the mistitled {Budget Summary} field is doing is concatenating several lookups so they just appear in one field on top of each other, simply because I like that display.
All fields represented in {Budget Summary}, which are lookups, are hidden so I can look at one field to compare a cost breakdown as opposed to 5, where some of those five are often blank.

[daphnedsm]

I have a similar need, would appreciate this feature. I have the need to put together a range of dollar amounts, such as $300-$500 which the format choice for formula fields won’t accommodate.

[Kamille_Parks]

Great point, I can think of many use cases when a user might want to show a range of prices in a cell.

[Michelle_Hugule]

I also would like to request a function that formats to currency. I have a column that the formula pulls a day of the week and an amount earned in to one entry, and because there is now text in the solution, the formatting loses the currency symbol and any zeros in the decimal value.

[W_Vann_Hall]

Just a reminder, a suite of ‘pretty-print’ routines can be found here that handle formatting of numbers or numerals with zero-padded decimal values; thousands dividers; user-specified thousands and decimal characters; left-, right-, or decimal alignment, even in grid views; use of parentheses or hyphen/hyphen-minus to indicate negative values; and more. The base includes an interactive table, as well, that allows one to enable/disable various options through checkboxes and see the effects. Several solutions of varying complexity are provided, to allow one to choose as light-weight an approach as possible.

[Kamille_Parks]

Thanks W, but again, its incredibly inconvenient for users to have to write formulas that complex for such a simple request.