Cannot sum cells - wrong format?

[Keddy44]

Hello,

I've got a problem with SUM function. I have a document where I do that, but the data is copied from another source (webpage), it is not entered by me.
That is the reason why I can't sum these cells, something is wrong with formatting. The question is how I can sum these cells without retyping their values (because I can easily have a thousands of rows and in that case it's hard to retype it all). Document is attached here: results.xlsx

The solution is probably easy but it's a problem to me, I cant solve that with right click -> change format to numbers?
Any other ways?

[popipipo]

=LEFT(D3,1)+RIGHT(E3,1)
Try this

[Tsjallie]

There's an (invisible) character with ascii code 160 in the cells. Probably coming along from the website.
You can get rid of it with

Please Login or Register  to view this content.

[joeu2004]

I've got a problem with SUM function. I have a document where I do that, but the data is copied from another source (webpage), it is not entered by me. That is the reason why I can't sum these cells, something is wrong with formatting. The question is how I can sum these cells without retyping their values (because I can easily have a thousands of rows and in that case it's hard to retype it all). Document is attached here: Attachment 396739
The solution is probably easy but it's a problem to me, I cant solve that with right click -> change format to numbers? Any other ways?

No, simply changing the Excel format does not remedy the problem.

The problem is: when you copy-and-paste from a webpage, some "spaces" are actually nonbreaking spaces (HTML nbsp). Excel does not recognize that as a space.

To remedy, put the following formula into a parallel column. For example:

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

SUBSTITUTE removes the nonbreaking spaces. TRIM removes any errant "real spaces" (none in your example). The double-negate converts the text result to a number.

The cell format General might suffice. Otherwise, choose a numeric format that you desire.

PS.... If you wish, copy the parallel column of results and paste-value over columns D and E to permanently replace the original text copy-and-pasted from the webpage.

PPS.... I try to avoid the problem in the first place by copying from the webpage and pasting into Notepad or equivalent non-formatting text editor, then copying from Notepad and pasting into Excel.

[samba_ravi]

=SUMPRODUCT(SUBSTITUTE(TRIM(D3:E3),CHAR(160),"")+0)
try this

[Keddy44]

Thank you, all suggestions worked well