Search code examples
excelcsvxlsxcurrency-formatting

How to format cells from a csv or xlsx file in excel with an ISO_4217 currency that can be reused for calculation

Issue description
I am exporting a table containing strings, numeric values, percentages and currencies to a csv or xlsx file for example:

CH0038863350,nominatives Nestlé SA,1,CHF 848.48,SHARE,6.72%
US0378331005,Stammaktien Apple Inc. 2,USD 118.64,SHARE,-55.13%

The issue that I have with the import in excel is, that it recognizes and formats some values as Currency and other values are just in the General format.

I would like to format the values in such a way, that I can reuse them for further calculation. The currency values are in the ISO_4217 format.

After some reasearch I have found out, that this is not so easy to do, because excel uses currency symbols ($,£,€, etc.) internally.

Questions
Do I have to convert my data to a specific format in order to work? Can I setup a custom format for my cell values that will support the desired format and then can still be used as a number for further calculation?

A simple workaround would be to export the numbers and the currency into two columns.

What would you suggest?

EDIT: The point was, that I wanted the format to be in one cell not in two (split of currency and value). The latter is fairly simple.

Solution

  • Click on the "Data Ribbon" and select "From Text/CSV". Choose your file and select "comma" as delimiter.

    enter image description here

    Next choose "transform data", highlight the column with the currency values, and choose "split column by delimiter".

    enter image description here

    Click "Close and load".

    Finally, if you don't want your table to stay connected to the csv-file, right-click on the query which was created and choose "delete"

    enter image description here

    There might be simpler ways to do this as well, but I think this should work for you.