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

[Nathan_Heironim]

+1 for me! This function would be fantastic!

[Edd_Read]

+1 this feels like a fairly sizeable omission

[Guido_Hoffmann]

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

[itoldusoandso]

Michelle_Huguley:

I also would like to request a function that formats to currency.

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:

Using a Lookup/Roll Up Field in a Formula Field Formulas

Your scenario works fine for me. If you compare your base with this one, you should be able to determine what went wrong.

[Creative_Renais]

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?

[Kamille_Parks]

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

[AlliAlosa]

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.

Currency Format in Email Automations

This should do the trick :slight_smile: Add a “Run a script” step to your automation, before the “Send Email” step. In the side bar on the left, add an input variable called currency and choose your {Currency} field. Use this code for the script: let inputValues = input.config(); let currency = inputValues.currency; //define function to convert numbers to currencies for report display function toCurrency(rawValue) { return "$ " + (Number(rawValue)).toFixed(2).replace(/\d(?=(\d{3})+\.)/g,…

[DamonH]

+1 for me. I use a formula to concatenate a budget line so that the linked record makes sense when you see it in the link. Right now it looks like ‘Smith Foundation - 100000’. With this feature, it would return ‘Smith Foundation - $100,000’. Thanks!

[CottageKeeper]

Count me in - I too need this feature! @Kamille_Parks you’re brilliant! :high_brightness: :high_brightness: :high_brightness:

[Rascode]

As we speak, I’m working on creating a formula title field that pulls in the “Fee” from a currency field. I scrolled through the entire list of functions only to find that no such native function exists.

@Kamille_Parks is absolutely right. A “Currency()” filter should be baked into the cake. No reaching for contrived solutions. So yeah, I’m a +1 on this suggestion.