Cells formated as numbers are calculating like text

[Guest]

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?

 

[CLR]

Sometimes this works..........

Data > TextToColumns > Delimited > Uncheck ALL Delimiters > Next > Insure
General format is selected > Finish

Vaya con Dios,
Chuck, CABGx3

 

[Ron Coderre]

Check to see if "numbers" have what appears to be a leading or trailing
space.
If yes, then edit the cell, paint through the "space" and press Ctrl+C to
copy, then press Escape

Next:
Edit>Replace
Find What: Click this field and press Ctrl+V (to paste in whatever the space
character is)
Replace with: Leave this blank (or delete it's contents)
Then click Replace (to make sure it's doing the right thing)
Then click Replace All

Does that solve your problem?

Ron

 

[Anne Troy]

You might want to try something like this in a neighboring column:
=trim(a1)*1
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Ron Rosenfeld]

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