On Wed, 6 Jul 2005 17:04:03 -0700, MM_BAM
wrote:

I have a workbook with several worksheets imported from a database. When I
check the format of the cells, they show up as Number with 2 decimal places.
BUT when I try to sum the values in a column it sums to zero (the values are
non-zero). If I try to add two cells in the range I get a #VALUE error.
Also, when I try to use the sum function from the tool bar, it does not
automatically populate any range.

It's acting like the cells are text, but the format says that they are text.
When I try top convert them to numbers:
1) there is no ! area that says they are text
2) when I use the "paste-special-multiply" to try to convert text to
numbers, I still get 0 as a result

ANY IDEAS/SUGGESTIONS?

The format of a cell does not directly give you information as to whether the
contents are TEXT or NUMBER. One way to detect that is with the
ISTEXT(cell_ref) function.

If the data is coming via the WEB, the most common cause for the behavior you
describe is the addition of a non-break space at the end of the number. This
is CHAR(160). To "clean up" the data, you could try this formula:

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

If that doesn't do it, by using the MID function you can step through the
contents of the cell one character at a time, and see exactly what you have.

For example, with A1 containing the number 23, followed by the no-break space,
do the following:

B1: =MID($A$1,ROW(),1)
C1: =CODE(MID($A$1,ROW(),1))

Select B1 & C1 and copy/drag down to row 4.

You should see the following:

2 50
3 51
* 160
#VALUE!

A similar strategy will let you analyze what's going on with your cells.

HTH,
--ron