Not able to Sum Column

[jrhguez]

From the attached file, the values on column C, E and F, I copied them directly from the Internet and so far I haven't been able to sum them up. So far I've tried the following:

1) text to column
2) trim them
3) value formula
4) reformat them as "general numbers"

and nothning has worked!....Any suggustions?

Thanks,
Jonathan

[contaminated]

U cannot sum 'em up because they are store as text, because u placed "$" singn in fron of these values. There are many array formulas that could help u. But the simpliest suggection is that to do Edit>Replace. In Find what field type "$" (w/o quotes) and leave blank replace with field. Then U van sum you values

[contaminated]

Here's it... fixed

[jrhguez]

Thank you very much....I did the replacement $, but even after doing that I couldn't make them to sum...What did you do other than to replace the $ to blank?...I would like to know for my own knowledge..

Thanks again
Jonathan

[contaminated]

First I copied all values in to another sheet.
Besides the dollar sign I have reepalced after copying, your thousands separators were commas. There's no problem while it wasn't typed manually. I had also replased these commas with Edit>Replace, and spaces after each value using text functions. Then I copied the results and pasted aonly values through paste special
Hope it helps.
If it is, marc your thread as "SOLVED" and dont forget to add reputation by pressing blie scales icon on the right of the desktop....

[contaminated]

Also This array formula does.
=SUM(VALUE(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C6:C70,"$",""),",","")," ",""),6)))
in C71
Bu better to use Edit>Replace

[jrhguez]

sorry I keep bothering....But which text function did you use?...I am trying to follow your instruction and I can't get it to work!

[martindwilson]

1. select columns with $ and , in
2. find & replace with nothing
3 find , replace with nothing
4 in a spare cell anywhere puy a zero 0 , copy the 0
select column(s) with text numbers in
right click
from the paste special menu under ....operation
choose "add "
click ok
delete the zero from wherever you put it,