Excel formatting
 

I copied a table from a web page and pasted it into an Excel spreadsheet.
When I try to @SUM the column numbers, I'm unable to do this. Excel returns
a result of zero. Why is this? I've tried to reformat the numbers but that
doesn't seem to help.

Excel formatting
 

you might have to make a new column and use the value() function to turn
them into numbers

"Cyndi" wrote:

I copied a table from a web page and pasted it into an Excel spreadsheet.
When I try to @SUM the column numbers, I'm unable to do this. Excel returns
a result of zero. Why is this? I've tried to reformat the numbers but that
doesn't seem to help.

Excel formatting
 

On Thu, 9 Mar 2006 08:52:19 -0800, Cyndi
wrote:

I copied a table from a web page and pasted it into an Excel spreadsheet.
When I try to @SUM the column numbers, I'm unable to do this. Excel returns
a result of zero. Why is this? I've tried to reformat the numbers but that
doesn't seem to help.

Most likely there are extraneous characters that cause Excel to see the numbers
as text.

You can remove extraneous spaces with the TRIM function; and you can remove the
no-break space (common in web documents) with the SUBSTITUTE function.

To convert each individual number to a "real" number:

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


To convert and SUM a range, in one step:

=SUM(--TRIM(SUBSTITUTE(rng,CHAR(160),"")))

entered as an array formula. To do this, after typing in the formula, hold
down <ctrl<shift while hitting <enter. Excel will place braces {...} around
the formula.

If there may be non-numeric data in rng (or blanks), then try the array
formula:

=SUM(IF(ISNUMBER(--TRIM(SUBSTITUTE(
rng,CHAR(160),""))),--TRIM(SUBSTITUTE(
rng,CHAR(160),""))))









--ron