Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
Formatting cells, Need help solving a problem with excel | Excel Discussion (Misc queries) | |||
Lost formatting in Excel | Excel Discussion (Misc queries) | |||
Excel conditional formatting error | Excel Discussion (Misc queries) | |||
Excel could not save all the data and formatting you recently ad.. | Excel Worksheet Functions |