Search code examples
excelcurrency

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
  • Expected: Sum of Dollars in column in Currency format.
  • Actual: No value displayed in sum cell.

Excel 365 version 2101 on Windows 10

Thanks

Solution

  • 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