Unable to sum numbers taken from websites in excel

[sylvia ong]

I copied numbers from a website to excel. I sum-ed the numbers up, but excel
seem to be unable to recognise them as numbers.
e.g:
8,735,279 13,037,465 4,932,955 7,304,148 7,538,364 --> sum =0

However, if i retype all these values manually, excel can sum the numbers
properly.

Do help!

 

[AltaEgo]

Your data are formatted as text. Try this:

Type a '1' in any cell.
Copy it.
Select all your pasted values.
Choose Edit, Paste Special and 'multiply'.

 

[sylvia ong]

Hi,

I have tried it but it doesnt work.

I found out the error, theres a space at the back of the number in the cell
which prevents excel from recognising it as a number.

Would u have any method to remove the space at the back?

 

[Dave Mills]

Some web sites have numbers with a spaces or other characters in 1st position.
This character does not display but it prevents Excel recognizing the values as
numbers. One such site is ADVFN.COM

Select one cell then look at the formula/text entry bar to see what is in the
cell.


On Tue, 22 Jul 2008 19:27:01 -0700, sylvia ong <sylvia

 

[Pete_UK]

You often get the non-breaking space character (code 160) when
downloading data from a web-site. You can get rid of them in one
operation by selecting all the affected cells and then CTRL-H (or Edit|
Replace), then:

Find what: Alt-0160
Replace with: leave blank
click Replace All.

Alt-0160 means hold the Alt key down while typing 0160 on the numeric
keypad.

You might still need to multiply those cells by 1 to convert them from
text to numbers - ensure the cell format is set to General first.

Hope this helps.

Pete

 

[Rick Rothstein \(MVP - VB\)]

Fixing your data is, of course, the best thing to do; but you can work
around the problem if you will be pasting in data from this same source over
and over again. Assuming the problem is non-breaking spaces (ASCII 160
code), this formula should be able to sum your values (just adjust the range
for where your data actually is)...

=SUMPRODUCT(--SUBSTITUTE(A1:E1,CHAR(160),""))

Rick

 

[Francis Tellis]

Unable to sum numbers taken from websites in excel

 

[Zaidy036]

Unable to sum numbers taken from websites in excel

<snip>

free ASAP Utilities has a function to convert text "numbers" to numerical
values

 

[Gord Dibben]

There are several suggested fixes posted in this thread.

Which of the those fixes have you tried?

Do you have spaces in the cells?

Linefeed characters?


Gord Dibben MS Excel MVP