Help

Csv download converts currency to text

Solved
Jump to Solution
1841 8
cancel
Showing results for 
Search instead for 
Did you mean: 
Sally_de_Witt
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions

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

When exporting to CSV and opening the CSV into Excel all four fields were recognized as numbers.
image

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.

See Solution in Thread

8 Replies 8

you 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

Hi @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:

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?

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

When exporting to CSV and opening the CSV into Excel all four fields were recognized as numbers.
image

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:
Screenshot 2021-09-29 at 05.01.29
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!
Screenshot 2021-09-29 at 05.05.56
Thank you so much for reminding me that Excel was converting the file, gave me the clue to investigate how it imported the data.