Help

Using German number format

Topic Labels: Formulas
8554 11
cancel
Showing results for 
Search instead for 
Did you mean: 
Saurabh_Surve
4 - Data Explorer
4 - Data Explorer

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

11 Replies 11
Mike_McLaughlin
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

5f73751092c6afb3485d0dfe997b3809227f5002.png

Formula field reference

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

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.

Eduardo_Pimente
6 - Interface Innovator
6 - Interface Innovator

+1
Number format is a much needed feature on Airtable!

thanks, but this does not work if we need the field to be a number instead of just text, does it?

Jan_Cascais
4 - Data Explorer
4 - Data Explorer

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.

Jose_Miguel_Gil
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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.

Jose_Miguel_Gil
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Henry31
4 - Data Explorer
4 - Data Explorer

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.