Hello,
HELP! Working with a base containing formatted currency fields but when downloading CSV, the resulting import into MS Excel, converts the currency fields to TEXT.
Is there a way to override this so that currency remains as a number field?
Thank you in advance
Kind regards,
Sally
Solved
Csv download converts currency to text
+1Best answer by Kamille_Parks11
Hi Dimitris
Nope - returns a #VALUE error
Something to do with the decimal I think
I made a simple table that had four test fields: a number, currency, formula formatted as currency, and a text field that looks like a currency.
When exporting to CSV and opening the CSV into Excel all four fields were recognized as numbers.
Keep in mind that all CSV files carry with them no formatting settings. Excel is always guessing how a cell value should be formatted when opening a CSV based on what it “sees”.
What exactly do your fields look like? Post a screenshot if you can. If Excel is recognizing neither Currency nor Number fields as proper numbers, that is unusual.
+4you cant turn it back into a number once its in excel? or turn it into a number field before you take it out of airtable? interested in why you’re pulling from airtable into excel its usually the other way around.
+1you cant turn it back into a number once its in excel? or turn it into a number field before you take it out of airtable? interested in why you’re pulling from airtable into excel its usually the other way around.
Hello Rebecca.
Thanks for he suggestion, BUT converting to a number before downloading the csv, still results in text, not number. Cannot be converted in Excel without retyping. Reads the decimal point as a full stop instead of decimal…
For now, will manually input the subtotal from Airtable, but if anyone else has any ideas please do advise.
RE: why the need for Excel… we are new to Airtable & still more comfortable with formatting for printing in Excel. Yes, I know we shouldn’t need to print, but … baby steps … The Team are still anxious when using Airtable as I have only recently converted the company database from Excel to Airtable - they’re still getting accustomed to the interface.
Thanks for your help
Kind regards,
Sally
+20Hi @Sally_de_Witt ,
I guess in your excel cell you have the following: $ 40, is that right?
If yes then you can use the following formula to convert the text automatically to number:
=value(RIGHT(B3,len(B3)-2))
- B3 is the cell with the info you want to convert.
- the function
RIGHT(B3,len(B3)-2)will keep the number part of the data - the
value()function converts the numeric text to number
If you want to apply this automatically to all the following cells of the same column you can use also arrayformula
Please text us back if it finally worked or if you want a different idea :slightly_smiling_face:
Thanks
+1Hi @Sally_de_Witt ,
I guess in your excel cell you have the following: $ 40, is that right?
If yes then you can use the following formula to convert the text automatically to number:
=value(RIGHT(B3,len(B3)-2))
- B3 is the cell with the info you want to convert.
- the function
RIGHT(B3,len(B3)-2)will keep the number part of the data - the
value()function converts the numeric text to number
If you want to apply this automatically to all the following cells of the same column you can use also arrayformula
Please text us back if it finally worked or if you want a different idea :slightly_smiling_face:
Thanks
Hello Dimitris,
I have the text: $25.35
I have tried as you have suggested above, but the formula will not work (have changed B3 to be my target cell reference)
:frowning:
+20Hello Dimitris,
I have the text: $25.35
I have tried as you have suggested above, but the formula will not work (have changed B3 to be my target cell reference)
:frowning:
Hi @Sally_de_Witt it is because there is no space between $ and the number. Please update by the following one:
function
RIGHT(B3,len(B3)-1)
Does it work?
+1Hi @Sally_de_Witt it is because there is no space between $ and the number. Please update by the following one:
function
RIGHT(B3,len(B3)-1)
Does it work?
Hi Dimitris
Nope - returns a #VALUE error
Something to do with the decimal I think
+27Hi Dimitris
Nope - returns a #VALUE error
Something to do with the decimal I think
I made a simple table that had four test fields: a number, currency, formula formatted as currency, and a text field that looks like a currency.
When exporting to CSV and opening the CSV into Excel all four fields were recognized as numbers.
Keep in mind that all CSV files carry with them no formatting settings. Excel is always guessing how a cell value should be formatted when opening a CSV based on what it “sees”.
What exactly do your fields look like? Post a screenshot if you can. If Excel is recognizing neither Currency nor Number fields as proper numbers, that is unusual.
+1I made a simple table that had four test fields: a number, currency, formula formatted as currency, and a text field that looks like a currency.
When exporting to CSV and opening the CSV into Excel all four fields were recognized as numbers.
Keep in mind that all CSV files carry with them no formatting settings. Excel is always guessing how a cell value should be formatted when opening a CSV based on what it “sees”.
What exactly do your fields look like? Post a screenshot if you can. If Excel is recognizing neither Currency nor Number fields as proper numbers, that is unusual.
Hi Kamille,
Thank you!
My fields contain text, numbers & multiple select fields in addition to the currency & formula fields:
BUT, you have just given me a clue - and I have managed to get it working but updating OS decimal & thousands separators and it now works perfectly!
Thank you so much for reminding me that Excel was converting the file, gave me the clue to investigate how it imported the data.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.