Autosum doesn't recognize cell values after I cut and paste valuesfrom web page

[Joe]

I just cut and pasted some numbers from a web pages and they went into
excel just fine but when I try to add them together using the autosum
feature it doesn't work, the sum is always 0. Is there some way I can
format the info to get the cells to recognize the values? I tried to
format the values to just numbers to no avail.

 

[Luke M]

The copied data probably contains extra spaces and is formatted as text. To
try and force it to be a number, try this:

Input 1 into a blank cell somewhere, copy cell. Select your data range,
right-click, paste special. multiply. That should force XL to make all the
data numbers, and you can then sum.

 

[Niek Otten]

=================================================================
Your Numbers don't behave (like numbers)
Niek Otten, May 11, 2006

Your numbers sort incorrectly, are not included in SUMs, cause #VALUE!
results in formulas, cannot be found in LOOKUPs, etc. In short:

Your Numbers look like Numbers, but they really are Text.
Sure! You formatted them as numbers, but alas, formatting afterwards doesn't
help. Believe me, they are Text!

Here's a checklist which will help you solve most known cases. Make a copy
of your workbook before trying! Always use Excel's ISNUMBER() function to
check your cells; maybe you solved your problem in the first step!

· Format an empty cell as Number. Enter the number 1 in it.
Edit>Copy.
Select your "numbers". Edit>Paste Special, check Multiply. Hopefully your
cells are "real" Numbers now
· If that doesn't help, there may be spaces in your "numbers". You
can use the LEN() function to compare the number of characters that Excel
sees in the cell with the number of characters you see. If you suspect
spaces, use Excel's TRIM() function to remove them
· If that doesn't help, there may be nonprintable characters in your
"numbers". You can use Excel's CLEAN() function to remove most of them
· If that doesn't help, there may be non-breaking spaces in your
"numbers" (mostly acquired from Web Pages). Use David McRitchie's TRIMALL()
function to remove them. It can be downloaded here:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

=================================================================

 

[מיכ×ל (מיקי) ×בידן]

Select the range of "numbers" > click on 'Data' > 'Text to Columns' > 'Finish'
Try to Sum, them, now.
Micky

 

[Joe]

Select the range of "numbers" > click on 'Data' > 'Text to Columns' > 'Finish'
Try to Sum, them, now.
Micky

I've tried every solution listed here and still the sum = 0. Is this a
lost cause?

 

[Gord Dibben]

Did you try this one?

Format all to General.

Copy an empty cell.

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

If no joy could be you have some HTML non-breaking spaces.

Select all cells and Edit>Replace

What: Alt + 0160 (using numpad)

With: nothing

Replace All


Gord Dibben MS Excel MVP