How do i copy numbers from Web and paste to excel and and get tota

[Guest]

When i copy number from a web page then paste them to a column i cannot use
the sum feature to get a total for the column. What can i do?

 

[Dave Peterson]

My bet is that you have extra (trailing???) spaces in the cell--or if you
copied|pasted from a web site, you could have some of those HTML non-breaking
spaces (char(160) in the cell.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

Depends on what has come over with the "numbers".

Sometimes the numbers come over as text.

Format all to General then copy an empty cell.

Select the "numbers" and Paste Special>Add>OK>Esc.

If that works you're OK

Sometimes you get extra spaces.

Try the TRIM function to get rid of spaces unless they are HTML non-breaking
spaces, which would require a different operation.

Post back if either of the first two don't do anything for you.


Gord Dibben MS Excel MVP


On Wed, 26 Jul 2006 13:32:02 -0700, jw austin <jw

 

[Harlan Grove]

jw austin wrote...

When i copy number from a web page then paste them to a column i cannot use
the sum feature to get a total for the column. What can i do?

Are you just pasting, e.g., just pressing [Ctrl]+V or running the Edit

Paste command? Or are you using Edit > Paste Special, as Text? If the former, try the latter, If the latter, it's likely the text includes trailing nonbreaking spaces (decimal character code 160). If that's the case, use Edit > Replace to get rid of them or sum using a formula like

=SUMPRODUCT(--SUBSTITUTE(Range,CHAR(160),""))