Feb 20, 2019 02:00 AM
Can I set-up AirTable to use the German number format which uses comma (,) for decimal place and dot(.) for thousand separator ? I mostly hace to create reports which require german formatting. It’s a real pain to download in excel and reformat it.
Thanks
Feb 20, 2019 01:37 PM
Did you try using the SUBSTITUTE() function?
If you need comma and decimal for a large number with high precision, I can see how this would may not work well.
For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...
Feb 21, 2019 12:44 PM
It’s not an ideal solution, but my numeric ‘pretty-print’ formatting routines allow you to specify decimal and thousands separators (as well as supporting a fixed number of decimal places, use of parentheses to show negative values, column alignment by decimal, and the like). They do result in the conversion of numeric values to text strings, so you may need to ‘shadow’ your numeric fields with equivalent text fields. Again, not perfect — but definitely simpler than having to export to Excel just to reformat.
Jul 22, 2019 09:59 AM
+1
Number format is a much needed feature on Airtable!
Aug 26, 2020 09:02 AM
thanks, but this does not work if we need the field to be a number instead of just text, does it?
Nov 14, 2020 08:01 AM
Hi Airtable team. Not having any other number formats then US (also for currency) makes airtable almost unusable. You cannot use formulas or similar you connot export to excel withoug creating custom import scripts.
Honestly any open source program can switch to international number or curreny format. That’s really a big downside.
Apr 17, 2022 08:23 AM
If you just need the numbers for final output, like when printing an invoice, not for any further calculation, you can use this modified W_Vann_Hall´s code to convert American formatted numbers into European ones, with the “EUR” symbol at the end, not at the beginning, of the number. Dots are changed for commas, and viceversa:
IF(
LEFT({number_to_convert}&’’,1,1)=’-’,
‘-’,
‘’)&
IF(
ABS(VALUE({number_to_convert}&’’))>=1000000,
INT(ABS(VALUE({number_to_convert}&’’))/1000000)&’.’&
IF(
INT(MOD(VALUE({number_to_convert}&’’),1000000)/1000)<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({number_to_convert}&’’),1000000)/1000)&’’)),
‘’)&INT(MOD(VALUE({number_to_convert}&’’),1000000)/1000)&’.’&
IF(
INT(MOD(VALUE({number_to_convert}&’’),1000))<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({number_to_convert}&’’),1000))&’’)),
‘’)&INT(MOD(VALUE({number_to_convert}&’’),1000)),
IF(
ABS(VALUE({number_to_convert}&’’))>=1000,
INT(ABS(VALUE({number_to_convert}&’’))/1000)&’.’&
IF(
INT(MOD(VALUE({number_to_convert}&’’),1000))<100,
REPT(‘0’,3-LEN(INT(MOD(VALUE({number_to_convert}&’’),1000))&’’)),
‘’)&INT(MOD(VALUE({number_to_convert}&’’),1000)),
INT(ABS(VALUE({number_to_convert}&’’)))))&’,’&
IF(
LEN(ROUND(MOD(VALUE({number_to_convert}&’’),1)*100,0)&’’)<2,
‘0’,
‘’
)&
ROUND(MOD(VALUE({number_to_convert}&’’),1)*100)
& ’ EUR’
Original thread for the code is here. Thank you W_Vann_Hall !!!
Nov 13, 2022 10:40 AM
Hi @Jose_Miguel_Gil,
Thanks for posting. At first i could not get it running. For me the solution was to replace ’ with ", since airtable might have changed something since you posted.
IF(
LEFT({number_to_convert}&"",1,1)="-",
"-",
"")&
IF(
ABS(VALUE({number_to_convert}&""))>=1000000,
INT(ABS(VALUE({number_to_convert}&""))/1000000)&"."&
IF(
INT(MOD(VALUE({number_to_convert}&""),1000000)/1000)<100,
REPT("0",3-LEN(INT(MOD(VALUE({number_to_convert}&""),1000000)/1000)&"")),
"")&INT(MOD(VALUE({number_to_convert}&""),1000000)/1000)&"."&
IF(
INT(MOD(VALUE({number_to_convert}&""),1000))<100,
REPT("0",3-LEN(INT(MOD(VALUE({number_to_convert}&""),1000))&"")),
"")&INT(MOD(VALUE({number_to_convert}&""),1000)),
IF(
ABS(VALUE({number_to_convert}&""))>=1000,
INT(ABS(VALUE({number_to_convert}&""))/1000)&"."&
IF(
INT(MOD(VALUE({number_to_convert}&""),1000))<100,
REPT("0",3-LEN(INT(MOD(VALUE({number_to_convert}&""),1000))&"")),
"")&INT(MOD(VALUE({number_to_convert}&""),1000)),
INT(ABS(VALUE({number_to_convert}&"")))))&","&
IF(
LEN(ROUND(MOD(VALUE({number_to_convert}&""),1)*100,0)&"")<2,
"0",
""
)&
ROUND(MOD(VALUE({number_to_convert}&""),1)*100)
& " EUR"
Also… save yourself some time and don’t change every {number_to_convert} in this code to your field’s name. Instead name you field {number_to_convert}, paste the code and change your field’s name to what ever you need.
Works like a charm.
@airtable get over yourself and make this a feature. Often times, money values are the most important of all data.
Nov 14, 2022 12:24 AM
Thanks for the update on this topic @Paul_Kalvelage !
Mi code is still working well without that change, but maybe other users can have a different setting and need your approach.
@airtable should definetely address this matter sooner that later, though.
Best,
Jose
Jan 01, 2023 03:12 AM
I'm also running in this problem. I want to switch my trading journal from Google Sheets to Airtable. But also for me, a real pain is that I can't change the whole workspace to German number formats, like I can do this in Google Sheet.
Is there somewhere an open feature request for this feature?
Why I want this: After a Trading Session, I put in A LOT of numbers. I'm using my Numpad on my keyboard a lot. There is a Comma in the Numpad area on German keyboard layouts. It's annoying when I now have to hit the dot in the middle of the keyboard, especially because I can't type it in blindly.
Two Screenshots attached, how Google Sheets worked it out.