Changing the format of an imported excel sheet from single line text to currency

Annalisa_Ferri · ‎Apr 28, 2020

Hi i face a problem
i paste a spreadshhet with numbers in it , a create a new base . When i want to change the fromat of the column from single line to currency , it gives back the smae number but divided by 100!

WilliamPorter · ‎Apr 28, 2020

Welcome to the Airtable Community!

So you’re saying that initially a field is a single-line text field with a value like (say) “775” in it, and after you convert the field type to currency, you see “$7.75”?

Odd. I’m not sure what you did but I cannot reproduce this error and have never seen it before. For a second I thought you’d made it a percentage field, but if you did, you’d see “7.75%” (with a percentage sign, not a currency symbol).

I just tested and it worked as I expect here. Here’s before:

And here is after converting field type to Currency:

Same value, just expressed as currency, with two decimal points and a US dollar sign.

I am sorry not to be able to tell you exactly what’s wrong. But I am pretty confident this is NOT how Airtable behaves. If you’d like to share your base with me privately I’d be happy to take a look.

William

Annalisa_Ferri · ‎Apr 29, 2020

Thank you!

Actually we found out that airtable thinks in US way , so what for us is a ‘comma’ “,” for decimal for it is a point “.”

Our problem is that we are Eu

Not sure if I have explained my self

WilliamPorter · ‎Apr 29, 2020

Okay, that’s interesting. Should have thought of that myself. But is Airtable not localized for EU users?

And I don’t quite understand. If I put a value like “972,45” in a text (single-line) field in Airtable, then convert the field to currency, the result I get is not the original number divided by 100 (as you said), but the original number multiplied by 100.

Unless somebody else joins this conversation and gives you the answer, I would suggest you try to reach Airtable support directly. Either that, or convert your values either in Excel prior to import or in Airtable after import. To convert in Airtable, create a new column/field with this formula

VALUE( SUBSTITUTE( EUNumAsText, ",", ".") )

And after you enter the formula, be sure you also click on the Formatting tab (same dialog), set the result to Currency with a precision of two decimal places, and specify the Euro symbol (€) as the currency symbol you want.

.

NOTE: The problem here is that the formula field is not editable directly. You’d have to use the other field for entry, and the formula field for viewing or exporting. That’s why it might make more sense to convert in Excel prior to import – and why it makes even more sense to localize Airtable.

Sorry I don’t have a better answer for you. Hope Airtable support does.

William