Carrie wrote to me and sent me her file back on 4/18. The space on the right of each cell
was a character 160. I had her use Find/Replace to remove them. And to make things more
interesting, trying to paste in a 160 character (copied from one of the cells) into the Find
what box could only be done via the Ctrl-v keyboard shortcut. Using Edit - Paste or the
Paste toolbar button caused it to paste the copied character into the first cell of the
selection, not the Find what box. We live in interesting times.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"frustrated in Fort Worth" wrote in
message ...
Thanks for the formulas and your time, but they didn't work. Excel still says
data value error. I can't afford to go thru each cell and eliminate the
right side space that is causing the trouble because there about 1000 cells
in a column, 2 columns per sheet, 4 sheets per month, and 12 months! That's
about 80,000 cells!
I also tried the trim option, nada. I have formatted to numbers with and
without decimals. I am totally at a loss. I know one piece of advise told me
to place "1" in an empty cell...but the cells aren't empty.
Am I allowed to paste a small piece of the column in here to show what the
problem is?

"Earl Kiosterud" wrote:

Fort Worth,

They're probably text, which Excel is utterly anal about (making us worry about whether
the
data type is number or text). Try one of these formulas:

=SUMPRODUCT((A2:A6)*1)

=SUM(VALUE(A2:A6))
(entered as an array formula (Ctrl-Shift-Enter instead of Enter))
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"frustrated in Fort Worth" <frustrated in Fort wrote in
message ...
I copy-pasted data from a database into an excel worksheet. When I use the
sum function on a column of numbers, excel says the sum is zero. I formatted
the cells to make sure it was number and not text. Still says zero sum. The
numbers are aligned to the right, but are indented one space. If I manually
delete that space then the number is recognized by excel. I can't figure out
how to get rid of the space in every cell and I don't know why it is there. I
am using excel 2003 and running xp. Also, I noticed the argument included a
"0" in it. For example =sum ("23";0;"13") With 23 and 13 being the numbers I
want to add and 0 being the space that I can't get rid of. The 0s are the
only thing excel sees in the cells and so the sum is always zero. Does
anybody know what is going on? And how to fix it?