# Add a CURRENCY() function for Formula Fields

cancel
Showing results for
Did you mean:
16 - Uranus

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

5 - Automation Enthusiast

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.

4 - Data Explorer

+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

18 - Pluto

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.

5 - Automation Enthusiast

+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.

8 - Airtable Astronomer

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

5 - Automation Enthusiast

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)
))))`````````
5 - Automation Enthusiast

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.

5 - Automation Enthusiast

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

6 - Interface Innovator

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

Recently Completed Ideas