I have a very simple spreadsheet with two columns of numbers. All I want to
do is add the numbers, which I understand I can do with the SUM function.

My problem is that each number has a blank space at the end of it, so the
sum function returns a "0" total when adding the columns. I determined I
could delete the blank space at the end of the numbers and the SUM would work
fine. However the spreadsheet has almost 200 entries and my fingers are
tired.

I cannot seem to get the TRIM function to work, and indenting the cells has
no impact on the formula working.

How can I get rid of these pesky spaces?

Thank you.

simple fix: do a find and replace (edit menu) for space characters and
replace them with nothingness, let me know if this works for you

"Cthulhu" wrote:

I have a very simple spreadsheet with two columns of numbers. All I want to
do is add the numbers, which I understand I can do with the SUM function.

My problem is that each number has a blank space at the end of it, so the
sum function returns a "0" total when adding the columns. I determined I
could delete the blank space at the end of the numbers and the SUM would work
fine. However the spreadsheet has almost 200 entries and my fingers are
tired.

I cannot seem to get the TRIM function to work, and indenting the cells has
no impact on the formula working.

How can I get rid of these pesky spaces?

Thank you.

Hi

TRIM should remove the space, however it doesn't necessarily change a
"text - number" into a real number
so TRIM your column in an adjacent column, then copy this column and choose
edit / paste special - values
now click in a blank cell somewhere (maybe on another worksheet) and copy
it - then select this column again and choose edit / paste special - ADD

this should set the numbers back to being numbers.

Hope this helps
Cheers
JulieD

"Cthulhu" wrote in message
...
I have a very simple spreadsheet with two columns of numbers. All I want
to
do is add the numbers, which I understand I can do with the SUM function.

My problem is that each number has a blank space at the end of it, so the
sum function returns a "0" total when adding the columns. I determined I
could delete the blank space at the end of the numbers and the SUM would
work
fine. However the spreadsheet has almost 200 entries and my fingers are
tired.

I cannot seem to get the TRIM function to work, and indenting the cells
has
no impact on the formula working.

How can I get rid of these pesky spaces?

Thank you.

ah, yes, you might have to format your cells as numbers instead of as
"general" which they may be by default. Julie makes a good point

"cwee" wrote:

simple fix: do a find and replace (edit menu) for space characters and
replace them with nothingness, let me know if this works for you

"Cthulhu" wrote:

I have a very simple spreadsheet with two columns of numbers. All I want to
do is add the numbers, which I understand I can do with the SUM function.

My problem is that each number has a blank space at the end of it, so the
sum function returns a "0" total when adding the columns. I determined I
could delete the blank space at the end of the numbers and the SUM would work
fine. However the spreadsheet has almost 200 entries and my fingers are
tired.

I cannot seem to get the TRIM function to work, and indenting the cells has
no impact on the formula working.

How can I get rid of these pesky spaces?

Thank you.

Take JulieD's suggestion one step further. In a blank column next to your
list of numbers enter the formula =VALUE(TRIM(number)) then copy and paste
values over your original column and delete the formula column. This will
remove the extra space and convert it to a number all at the same time.

"JulieD" wrote:

Hi

TRIM should remove the space, however it doesn't necessarily change a
"text - number" into a real number
so TRIM your column in an adjacent column, then copy this column and choose
edit / paste special - values
now click in a blank cell somewhere (maybe on another worksheet) and copy
it - then select this column again and choose edit / paste special - ADD

this should set the numbers back to being numbers.

Hope this helps
Cheers
JulieD

"Cthulhu" wrote in message
...
I have a very simple spreadsheet with two columns of numbers. All I want
to
do is add the numbers, which I understand I can do with the SUM function.

My problem is that each number has a blank space at the end of it, so the
sum function returns a "0" total when adding the columns. I determined I
could delete the blank space at the end of the numbers and the SUM would
work
fine. However the spreadsheet has almost 200 entries and my fingers are
tired.

I cannot seem to get the TRIM function to work, and indenting the cells
has
no impact on the formula working.

How can I get rid of these pesky spaces?

Thank you.

Thank you all for your suggestions.

SauveC--

I did try your suggestion combined with Julie's. It worked perfectly for
the first cell. When I tried to drag the fromula down the rest of the
worksheet, however, I get a "#VALUE" error. Now what? :)

I can't duplicate your error. You should have been able to copy it down
without any problem if it worked for the first entry. Another function that
can be used is CLEAN in place of TRIM in the formula. See if that makes any
difference.

"Cthulhu" wrote:

Thank you all for your suggestions.

SauveC--

I did try your suggestion combined with Julie's. It worked perfectly for
the first cell. When I tried to drag the fromula down the rest of the
worksheet, however, I get a "#VALUE" error. Now what? :)

Select all your data and do Edit / Replace, replacing space with noithing,
as already suggested. Then type a 1 in a cell somewhere, copy the cell,
select all your data and do Edit / Paste Special / values & multiply, or
copy an empty cell and select all and do Edit / Paste Special / values and
Add.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Cthulhu" wrote in message
...
Thank you all for your suggestions.

SauveC--

I did try your suggestion combined with Julie's. It worked perfectly for
the first cell. When I tried to drag the fromula down the rest of the
worksheet, however, I get a "#VALUE" error. Now what? :)

Wow, I seem to be so close. I don't understand why the formula won't carry
down the column without an error.

I also do not know how to replace space with nothing. What am I typing for
"FInd what" and "Replace with"?

"Ken Wright" wrote:

Select all your data and do Edit / Replace, replacing space with noithing,
as already suggested. Then type a 1 in a cell somewhere, copy the cell,
select all your data and do Edit / Paste Special / values & multiply, or
copy an empty cell and select all and do Edit / Paste Special / values and
Add.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Cthulhu" wrote in message
...
Thank you all for your suggestions.

SauveC--

I did try your suggestion combined with Julie's. It worked perfectly for
the first cell. When I tried to drag the fromula down the rest of the
worksheet, however, I get a "#VALUE" error. Now what? :)

You don't need any formulas with the instructions I gave you

When you get the Find/Replace box up, put the cursor in the Find What bit
and hit the space bar once. Leave the Replace with empty and hit OK.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Cthulhu" wrote in message
...
Wow, I seem to be so close. I don't understand why the formula won't
carry
down the column without an error.

I also do not know how to replace space with nothing. What am I typing
for
"FInd what" and "Replace with"?

"Ken Wright" wrote:

Select all your data and do Edit / Replace, replacing space with
noithing,
as already suggested. Then type a 1 in a cell somewhere, copy the cell,
select all your data and do Edit / Paste Special / values & multiply, or
copy an empty cell and select all and do Edit / Paste Special / values
and
Add.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"Cthulhu" wrote in message
...
Thank you all for your suggestions.

SauveC--

I did try your suggestion combined with Julie's. It worked perfectly
for
the first cell. When I tried to drag the fromula down the rest of the
worksheet, however, I get a "#VALUE" error. Now what? :)

What: enter a space using spacebar.

With: do not enter anything

Perhaps TRIM won't work because you have an HTML non-breaking space in the
cell.

To get rid of that....editreplace

What: hit ALT + 0160 using the numpad

With: do not enter anything


Gord Dibben Excel MVP

On Thu, 10 Feb 2005 10:17:05 -0800, "Cthulhu"
wrote:

Wow, I seem to be so close. I don't understand why the formula won't carry
down the column without an error.

I also do not know how to replace space with nothing. What am I typing for
"FInd what" and "Replace with"?

"Ken Wright" wrote:

Select all your data and do Edit / Replace, replacing space with noithing,
as already suggested. Then type a 1 in a cell somewhere, copy the cell,
select all your data and do Edit / Paste Special / values & multiply, or
copy an empty cell and select all and do Edit / Paste Special / values and
Add.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Cthulhu" wrote in message
...
Thank you all for your suggestions.

SauveC--

I did try your suggestion combined with Julie's. It worked perfectly for
the first cell. When I tried to drag the fromula down the rest of the
worksheet, however, I get a "#VALUE" error. Now what? :)