Excel not recognizing numbers in cells?

Question

I copied some numbers on a web page and pasted them onto an excel spreadhseet.  When I use the sum formula, or average formula, excel is not recognizing the numbers in the cell, and won't  add up my rows.  I've tried reforamtiing the field as a number, but it's not working.  If I re-type the numbers in the field, then Excel recognizes them.  This is frustrating!!  Help!!

Answer 1

Follow up comment to my last message...

I note you say you copied your numbers from a web page; that means it is possible that your spaces are not ASCII 32 spaces, but rather are ASCII 160 non-breaking spaces. If my previous suggestions doesn't work for all your numbers, go back to the Replace dialog bog and remove the space character that is in the "Find what" field and enter this keystroke combination into that field in its place... ALT+0160 but you MUST type those four digits from the NUMBER PAD, not the main keyboard.... then click the "Replace All" button.

Answer 2

Check out whether there are spaces before or ater the cellss..If so try replacing the spaces.

Another way to easily convert these cells to numeric format if you have enabled error checking for these cells. Check whether the cells are in text format. (Right click>FormatCells). To convert the cells to numerics do the below.

In 2003 Tools>Options>Error checking>'Number stored as text'

In 2007 OfficeButton>ExcelOptions>Formulas>Error checking>

--If you have this option checked; then error checking is enabled for such cells.

--For cells with numeric value but formatted as text; on the left top corner of the cell you will see a green triangle.

--Select the range of cells and make sure one of the cells with the green triangle is the active cell (cell with white background).

--Click/dropdown on the error information popup which is displayed towards the left of the active cell

--Select 'Convert to number'

Yet another work around is

--Copy a blank cell

--Keeping the copy select the range of cells with numeric values

--Right click>PasteSpecial>

--Select 'Add' and click OK.

Answer 3

Thank you so much! This worked for my calculation!

>Yet another work around is

->-Copy a blank cell

->-Keeping the copy select the range of cells with numeric values

->-Right click>PasteSpecial>

->-Select 'Add' and click OK.

Answer 4

genius!

Answer 5

Hi Rick,

One more question if you are able to help.  When I cut and paste the numbers into the cells, I want them to display as a percentage.  Obviously I would format by selecting the percentage option, however when I do that, a number like 56, becomes 5600%.  I did a formula (=56/100) and copied that formula in the entire row, so that displayed the correct percentage.  But I would have thought there is a feature to for the percentage format NOT to multiply by 100.  Any advice?  Why can't a cut and paste, simply be a cut and paste??  :-)

Thank you!!