CODEWITHSUNDEEP
excelcurrency
SystemsInCode
SystemsInCode

Reputation: 699

Imported currency symbol in Excel cell $ is not 'auto' converted when I format as currency

I import a bank statement that has $ symbol in cell contents. I was expecting Excel to accommodate this when I format the cell as Currency (Dollar) and remove the $ from the value but have as the format displayed.

However this does not seem to be the case. I have to manually remove the symbol and then format as currency. I need to do this in order for a simple column sum to work.

Any ideas on the right 'process' to use to convert csv data so I don't have to do a find/replace?

Steps to reproduce:

  1. Open CSV with column that has $ in each cell prefixing the value.
  2. add column sum total to the bottom of the column
  3. Convert column formatting to currency

Excel 365 version 2101 on Windows 10

Thanks

Upvotes: 0

Views: 943

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

In general, it is best to import csv files. Then you can inform Excel of the data type before it is processed (often incorrectly).

In O365, I suggest you start with Data=>Get&Transform=>from Text/CSV. If any columns seem to have been interpreted incorrectly, you can select Transform. Otherwise, just accept the defaults.

If the CSV file is updated, the query can also be updated.

The legacy wizard is also available, but is less flexible

Upvotes: 1

Related Questions