Add a CURRENCY() function for Formula Fields

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

7 Likes

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.

Screen_Shot_2018-12-20_at_1_54_49_PM

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.

3 Likes

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.

3 Likes

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

17%20PM

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

3 Likes

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

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.

3 Likes

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.

4 Likes

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

1 Like

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.

1 Like

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.

1 Like

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

+1 for me! This function would be fantastic!

+1 this feels like a fairly sizeable omission

Would also +1 the ability to format numbers within a formula like this.

Seems to me the need would be also to reformat a number output (as text from another formula) into plain number format. e.g. IF(Column1=ABCD,“6666”,“tryagain”) will always produce text string, whether the result is 6666 or tryagain. So the result can’t be used in subsequent formulas. Still looking for answers how to solve it.

UPDATE: OK sorry, I found a solution to my “Number” format problem. Crazy I need to add another formula to do something so simple. Formula needded is … VALUE({NumberIWant}&’’)

This is not giving any answer to the question of the OP but I am leaving this here just in case somebody looking for the answer, it seems related to me.

Source:

Hi Kamille, I tried this formula but when I click ‘Formatting’ to see how it looks it says “Your field is not fully configured. You need to have a valid field configuration in the formula tab before adding formatting options”

What did I miss?

@Creative_Renaissance You’ve missed nothing, you can’t format the field as anything other than a string because it returns a string.

1 Like

I’d still love a CURRENCY() function, but here’s a solution in the meantime using the scripting app! This particular use-case involved formatting just one number prior to sending it in an email, but could easily be adapted.

1 Like