Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Number format (##,###,###.##) with comma or other separators

cancel
Showing results for 
Search instead for 
Did you mean: 
Mark_Marsiglio1
5 - Automation Enthusiast
5 - Automation Enthusiast

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

61 Comments
Serg_Gross
4 - Data Explorer
4 - Data Explorer

WhatsApp Image 2022-03-18 at 18.46.47
The solution is clunky, but it works…

Col: <=999999999.99
Subtotal: 999999999,99
Result with subtotal: 999 999 999,99
Result: 999 999 999,99

Subtotal:
IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",",")))

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!")))

Result:
IF(LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))))<7,IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))),IF(LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))))<10,MID(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))),1,LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))))-6)&" "&RIGHT(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))),6),IF(LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))))<13,MID(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))),1,LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))))-9)&" "&MID(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))),LEN(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))))-8,3)&" "&RIGHT(IF(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",","))="",SUBSTITUTE(CONCATENATE({Col}),".",",")&",00",IF(LEN(SUBSTITUTE(CONCATENATE({Col}),".",","))-(SEARCH(",",SUBSTITUTE(CONCATENATE({Col}),".",",")))=1,SUBSTITUTE(CONCATENATE({Col}),".",",")&"0",SUBSTITUTE(CONCATENATE({Col}),".",","))),6),">= billion!")))

Tobias_LGKR
7 - App Architect
7 - App Architect

+1

It really much harder to read long numbers without seperators.

Alex_Whitton
6 - Interface Innovator
6 - Interface Innovator

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)

image

image

Serg_Serg
4 - Data Explorer
4 - Data Explorer

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.

kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable Community!

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.

Alex_Whitton
6 - Interface Innovator
6 - Interface Innovator

Kuovonne awesome thank you, will purchase cheap at the price

CodelessFactory
4 - Data Explorer
4 - Data Explorer

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

  1. Copy this formula to an text editor (We use Visual Studio Code)
  2. Find and replace all “{REPLACE_WITH_YOUR_FIELD}” To your field. Your field should be a number field with 2 decimals.
  3. 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")))," ",".")))

Alvaro_Hernande
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you! It works and most important. It´s free! :slightly_smiling_face:

E_Herman
4 - Data Explorer
4 - Data Explorer

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.

Gillian_Gainsle
5 - Automation Enthusiast
5 - Automation Enthusiast

wahoo! this solved my problem – thank you so much!

Would still be nice to build some formatting into the regular number field, but this is a perfect workaround. thanks.