This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

For readability it would be nice to be able to present numbers (especially large ones) with separators such as commas. Custom defined number formats would be even better (regex).

Result with subtotal: IF(LEN(Subtotal)<7,{Subtotal},IF(LEN(Subtotal)<10,MID({Subtotal},1,LEN(Subtotal)-6)&" "&RIGHT({Subtotal},6),IF(LEN(Subtotal)<13,MID({Subtotal},1,LEN(Subtotal)-9)&" "&MID({Subtotal},LEN(Subtotal)-8,3)&" "&RIGHT({Subtotal},6),">= billion!")))

I found this shortly after the post - which suited my needs - to use a formula and change formatting to โcurrencyโ but just remove the โ$โ sign (deleted where green highlight is)

I need a different number formatting: I need to separate the decimals with a โ,โ and separate the groups of digits with a space.
If you need a different separator, then in โResult with subtotalโ you need to replace " " with the desired character.

My app, Ready Made Formulas can create a formula that formats a number as you describe. This particular formula requires a premium license, which can be very affordable compared to the time spent wrestling to get a formula to produce the exact format you want.

Hi All, we combined some answers on this topic to an easy to implement solution. Follow these steps to implement:

Copy this formula to an text editor (We use Visual Studio Code)

Find and replace all โ{REPLACE_WITH_YOUR_FIELD}โ To your field. Your field should be a number field with 2 decimals.

Paste the edited formula in a new Airtable field.

CONCATENATE("$ ",(SUBSTITUTE(IF(LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))))<7,IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))),IF(LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))))<10,MID(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))),1,LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))))-6)&"."&RIGHT(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))),6),IF(LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))))<13,MID(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))),1,LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))))-9)&"."&MID(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))),LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))))-8,3)&"."&RIGHT(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))="",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")))=1,SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",",")&"0",SUBSTITUTE(CONCATENATE({REPLACE_WITH_YOUR_FIELD}),".",","))),6),"Max number for this formula 999.999.999,99")))," ",".")))

Oh my gosh!! Thank you Alex, this is all I needed. I struggle to understand why a number field canโt include the type of formatting made available to currency. Meanwhile, you nailed it without the hassle, saving me I donโt know how many hours.