Number Format:This prevents me from summing cells.

[rojo]

I'm having problems changing the format of certain cells. I've coppied and pasted my account statement into excel, and I want to categorize and sum various transactions. The problem is, I can't change the format for the data in the transaction amount column. For example, a transaction amount reads $100.00. I can't change it to text, general, currency, percentage, etc. It remains in the cell as $100.00. This prevents me from summing cells.

I've tried recopoying these cells and pasting special with just the values but that didn't work. Also, when pasting my original statement into excel I tried pasting special as text. But when I past as text, it puts the entire line item in one cell, so the date, description, transaction, balance, etc. all in one cell.

When I "paste special" my statements as html, each category has it's own column, but I can't reformat the cell to sum transactions.

Any ideas? thanks in advance....

[DonkeyOte]

What happens if you run an Edit -> Replace on the column containing $ values and replace: $ with : nothing

Assuming the above does not work we'd need to say a little sample to see if there are other characters affecting the output (Char(160) being a common problem)...

[rojo]

Thanks for your reply. I had tried that but it didn't work. Not sure what the issue is.

I attached a worksheet with a few sample transactions. The $ is removed from the amounts. If you can come up with anything I'd greatly appreciate it.

[DonkeyOte]

Usual suspect ... Char(160)

Run an Edit -> Replace on the values

Replace What: hold ALT and type 0160 on numeric keypad
(if using laptop hold FN + ALT and type 0160 on numeric keypad)

Replace With: nothing

Should resolve issue.

[rojo]

Worked like charm. Thank you so much!