Share via

Cannot add up currency values from CSV file in Excel

Anonymous
2021-08-07T07:21:31+00:00

I know there are reams of entries on this subject and the few I've read don't work. I am simply trying to add up currency values received in a CSV file, here's a small sample:

I've tried various things but to no avail. It seems Excel refuses to treat the format as anything other than characters. For example, I've tried stripping out the first character (the "£" sign) just leaving the numbers, but that doesn't work. I'd welcome a quick and simple solution.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-08-07T12:08:30+00:00

    > ... I'd welcome a quick and simple solution.

    Hi. With your 365 version, you do not have to do all that.

    The ' is called a prefix character that ensures the entered data is text.

    A1:A5 have that prefix character below.

    Image

    Again, this is if you want just a quick SUM. With anything more elaborate, I would convert the text to numbers as mentioned above.

    0 comments
  2. Anonymous
    2021-08-07T11:20:51+00:00

    I'm afraid this didn't work. In the video, one of the fields in question shows up in the formula bar with an apostrophe in front of it, so $428 appeared as '$428

    When I do similar to mine by selecting cell C2, you can see there's no leading apostrophe....

    I tried paste special ADD and MULTIPLY, but to no avail.

    0 comments
  3. Anonymous
    2021-08-07T10:43:40+00:00

    re: data won't sum

    As Palcouk kinda said, numbers with currency symbols are not numbers.
    Numbers can include currency symbols but they must be added to the cell using cell formatting.
    So your first step is remove the currency symbols using Find and Replace.

    Then see the various options at...

    "Fix Excel Numbers That Don't Add Up" https://www.contextures.com/xldataentry03.html

    My favorite is:

    1. enter 1 in a blank cell
    2. copy that cell
    3. select the data you want to convert to numbers
    4. use Paste Special / Multiply

    1. numbers then should be numbers

    '---

    NLtL

    https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    (the free 'Professional Compare' compare workbook has a Clean Data option)

    0 comments
  4. Anonymous
    2021-08-07T07:30:23+00:00

    I use csv files downloaded from third party sites all the time but they do not include the currency symbol, all formulas, eg AutoSum work as expected

    In the csv's I tested the billing columns were all formulated as 'General'

    0 comments