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

[Bbb_Shell]

Is there a way to use/tweak the script to run it in the table itself? I need it for displaying in Softr webapp. I was hoping after running the script, the string version would be created in a given field and would keep the currency, comma and decimal.

[Laurie_Stark]

+1 this request - I’d like this for a formula so that the field that displays in the calendar view of my budget is something like $20 - American Express

[kuovonne]

Laurie_Stark:

I’d like this for a formula so that the field that displays in the calendar view of my budget is something like $20 - American Express

My app Ready-Made Formulas will generate a formula that does this. You can pick from several different common formats, or you can choose a custom format.

For a custom format, you can enter a currency symbol, how many decimal places, and several other options.

This formula does require a premium license, but it saves a lot of time versus finding and implementing less robust formulas on the internet. Plus, one license grants access to all the formulas in the app for one base.

[Dalkat]

+1 for this!!

I get a lot of numbers that display as “13.5” instead of “13.50” or “$13.50” when in a string. Super frustrating that I can’t turn it into a currency via formula, since I can’t do it via formatting.

[GavinA]

Currency Converter - Apps - Airtable Marketplace may help here as well! Let us know any missing features required!

[Brandon_Gilbert]

I needed a similar solution that accounted for cents in currency. I’m using Zapier to connect Airtable data for custom proposals to my WordPress site that displays stylized/dynamic proposals. The data in JSON doesn’t retain the currency formatting, making my pricing tables a little hard to read. By default, a raw string won’t have “.00”, and if the string does have a tenth value but not a hundredth value (such as “0.50”), the output will not have an ending “0” (in the case of “.50”, the result will be 0.5").

These two issues with cents required a few nested solutions. I forked @Kamille_Parks initial formula and added my solutions for cents/decimals. Adding the decimals did require tweaking Kamille’s beautiful formula to account for the three extra characters, the decimal, and the two numbers after it. Here’s what I have, be sure to substitute/Find & Replace my {Subtotal} field for your own currency-based field that you want to format as a string):

IF(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<7,IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),
  IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>6,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<10),
LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6),
IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>9,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<13),
  LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-9)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),9),6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6),
  IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>12,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<16),
    LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-12)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),12),6)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),9),6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6)
  ))))```

[Tristan]

This is great, but I find it blanks when there’s a 0 in the hundreds place. Not sure if it also blanks when 0s are in other places. E.g., referencing $176,011.66, the formula produces a blank.

[Keri_Caraher]

THANK YOU BRANDON!!! :grinning: :100: :index_pointing_at_the_viewer:

[Jeri_Vespoli]

Adding my vote to a Currency() and/or Number({numeric field}, format) function!
Using a numeric field in a formula results in a string, dropping the zeros, and adding +0 does not convert it back to a number. The LEN function is way too much logic for something so simple. @AlliAlosa

This: CONCATENATE(IF({Salary To}>’’,‘Salary to: $’&{Salary To},’’),’ ‘,IF(AND({Salary Type}=‘Hour’,{Salary To}>’’),‘HR’,IF(AND({Salary Type}=‘Year’,{Salary To}>’’),‘YR’,’’)))

should be able to be easily formatted to produce this: Salary to: $23.50 HR not this: Salary to: $23.5 HR