cell is not empty but excel thinks it is,,,why?

  • Thread starter frustrated in Fort Worth
  • Start date
F

frustrated in Fort Worth

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?
 
E

Earl Kiosterud

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))
 
K

Ken

Fort Worth ... (Good morning)

1: Place "1" in an empty cell
2: Select the cell & COPY
3: Select your range of numbers with the leading space
4: PASTE/SPECIAL Multiply
5: Your Range Now formatted Numbers (no leading space)
6: Your SUM Formula should work

Kha
 
J

Jon Peltier

Simpler:

1. Copy an empty cell.
2. Select the range of numbers to convert.
3. Paste Special - Operation - Add

- Jon
 
F

frustrated in Fort Worth

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?
 
F

frustrated in Fort Worth

Thanks Jon,
The cells are not empty. They have numbers in them, but they also have the
space to the right side of the number and that space is all that Excel is
seeing. Somehow that space is a "0" and Excel thinks it is the only thing in
the cell. I have reformatted the cell to be numbers and not text, with and
without decimals.
The only time I can get excel to see the actual number in the cell is if I
go in there and manually remove the space. With 80,000 cells, removing the
space manually is not an option
Any other suggestions????? I hope!
 
S

Sandy Mann

Try using a Helper Column with the formula:

=--SUBSTITUTE(D2,RIGHT(D2,1),"")

and drag down using the fill handle.

Then copy and Paste Special > Values over the original data and then delete
the Helper Column.

Warning: If any of your *numbers* not not have a space on the right-hand
side then the last digit will be removed by this method.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
J

Jon Peltier

Maybe I should have made my instructions more clear:

1. Copy an empty cell which is really empty and has nothing to do with the
cells you have problems with (so that you're copying a zero value).
2. Select the range of cells you are having trouble with.
3. Paste Special - Operation - Add, which adds the copied zero to the
misbehaving cells, coercing them to be interpreted as numbers.

- Jon
 
F

frustrated in Fort Worth

Hats off to Sandy! He identified the trouble as character 160 and gave me
the tools to get rid of it. Everything works great now! THANK YOU SANDY!!!!
 
E

Earl Kiosterud

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
-----------------------------------------------------------------------
 
E

Earl Kiosterud

Jon,

I'm sure you could. And that's easier. But the thing I found odd was that Ctrl+v worked
differently than Edit - Paste or the paste button.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top