Excel cannot sum columns when pasting from web page

[veronica madsen]

hello, i have a problem with summing data that i copy from the web.
sometimes i am on page and i select rows from a table and paste into
my Excel 2003 document. everything pastes fine and the columns are
all there. however i am unable to sum the columns that appear to be
numeric. i get a #VALUE! error. so then i select the cells and
change the format to numeric but it still doesn't work. anyone have
any suggestions?

thanks,

v.

 

[Frank Kabel]

Hi
after changing the format try:
- select an ampty cell and copy this cell
- select your imported data
- goto 'Edit - Paste Special' and choose the action 'Add'

 

[Guest]

Try typing 1 into a cell. Copy that cell.
Then, Select all the cells you brought in from the web and Select Edit |
Paste Special, Multiply.

If that DOESN'T work, you may have some blank spaces in the cells. so, step
two would be to try this:
Create Helper Column(s) and use the following formula:
=TRIM(A1)
You'll need to change the cell reference to match your first number you need
to change. Copy down and/or to the right.

tj

 

[Guest]

Forgot to mention:
If you use the second method:
Copy the new data generated by the formula.
Then, Edit | Paste Special, Values.

tj

 

[Ron Rosenfeld]

hello, i have a problem with summing data that i copy from the web.
sometimes i am on page and i select rows from a table and paste into
my Excel 2003 document. everything pastes fine and the columns are
all there. however i am unable to sum the columns that appear to be
numeric. i get a #VALUE! error. so then i select the cells and
change the format to numeric but it still doesn't work. anyone have
any suggestions?

thanks,

v.

Are you summing using the SUM function, or are you adding using the addition
operator?

If the former, I don't know why you are getting a #VALUE! error from t he
information you provide.

If the latter, then the problem is that the numbers from the web are TEXT and
not real numbers. Also they likely have a non-printing space at the end.

Try this:

If your data is in A1:An, in some column put the formula:

=--SUBSTITUTE(TRIM(A1),CHAR(160),"")

That should convert it into a number. Copy/drag the formula down as far as is
necessary.

Then copy/paste special Values over the original and your addition should work
OK.

(Make a backup of your original data first).


--ron