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?