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?

If you take out the quotation marks, you should get the result you expect.
--
HTH,
Barb Reinhardt



"frustrated in Fort Worth" wrote:

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?

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?

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?

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?

Couldn't you use Alt+1060 (keypad)?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Earl Kiosterud" wrote in message
...
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?

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
-----------------------------------------------------------------------
"Jon Peltier" wrote in message
...
Couldn't you use Alt+1060 (keypad)?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Earl Kiosterud" wrote in message
...
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?

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




"frustrated in Fort Worth" wrote:

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?

Simpler:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ken" wrote in message
...
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




"frustrated in Fort Worth" wrote:

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?

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!

"Jon Peltier" wrote:

Simpler:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ken" wrote in message
...
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




"frustrated in Fort Worth" wrote:

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?

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


Replace @mailinator.com with @tiscali.co.uk


"frustrated in Fort Worth"
wrote in message ...
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!

"Jon Peltier" wrote:

Simpler:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ken" wrote in message
...
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




"frustrated in Fort Worth" wrote:

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?

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

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"frustrated in Fort Worth"
wrote in message ...
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!

"Jon Peltier" wrote:

Simpler:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ken" wrote in message
...
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




"frustrated in Fort Worth" wrote:

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?

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"frustrated in Fort Worth"
wrote in message ...
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!

"Jon Peltier" wrote:

Simpler:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ken" wrote in message
...
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




"frustrated in Fort Worth" wrote:

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?