Excel not recognizing numbers in cells?

Anonymous

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!!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

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.

0 comments

Answer accepted by question author

Anonymous

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.

0 comments

49 additional answers

Anonymous

2017-11-19T10:16:47+00:00

Hi,

Try this, put 1 in an empty cell and copy that cell, select the range to convert, paste special, multiply.
Was this answer helpful?

0 comments No comments
Anonymous

2017-11-18T23:43:40+00:00

I have the same question. I have copied paste numbers from a website and Excel is not recognizing these. I have tried all the conversion options that excel is giving me but still the problem remains.

The problem is that I can not remove the text qualifier (') from the cells with any option given.

I have to go on each cell and remove these one by one. I would like to know if there is a more efficient option exist to do this as the abovementioned method that I'm using is very time consuming.
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-15T23:16:20+00:00

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.

Another quicker way around that is if it's a single column of numbers, paste into Notepad first, then copy it back. That will remove all formatting.
Was this answer helpful?

0 comments No comments