Unable to sum numbers taken from websites in excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sylvia ong - 23 Jul 2008 03:27 GMT

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!

Reply to this Message

AltaEgo - 23 Jul 2008 04:33 GMT

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'.

Signature

Steve

> I copied numbers from a website to excel. I sum-ed the numbers up, but
> excel
[quoted text clipped - 6 lines]
>
> Do help!

Reply to this Message

sylvia ong - 23 Jul 2008 04:40 GMT

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?

> Your data are formatted as text. Try this:
>
[quoted text clipped - 13 lines]
> >
> > Do help!

Reply to this Message

Pete_UK - 23 Jul 2008 09:41 GMT

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

On Jul 23, 4:40 am, sylvia ong <sylvia...@discussions.microsoft.com>
wrote:

> Hi,
>
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Jul 2008 18:33 GMT

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

> Hi,
>
[quoted text clipped - 24 lines]
>> >
>> > Do help!

Reply to this Message

Dave Mills - 23 Jul 2008 05:52 GMT

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.

>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.
[quoted text clipped - 5 lines]
>
>Do help!

Signature

Dave Mills
There are 10 type of people, those that understand binary and those that don't.

Reply to this Message