Excel returning "0" when summing

[PhenyxFire]

Hello!

I am having problems getting Excel to sum a column in a series of
spreadsheets. The information comes from a SQL query and is imported into
Excel. The problem is in column F, rows 2-141. No matter how I format the
cells, I can't get a total. I have tried multiple suggestions found on the
newsgroup (trim and clean macros, edit/replace with holding the alt key and
typing 0160, etc.) and nothing works. The only way I can get it to total is
copy/paste cells 2-141 into Word, then copy them out of Word and paste back
over the same cells in Excel. It will calculate then, so something is being
stripped out and allowing the calculation.

If there were only a few spreadsheets to deal with, I would do the
copy/paste in and out of Word each time. Unfortunately, there are 200
spreadsheets a week that are run and having this problem. I can't keep doing
the copy/paste in and out of Word 200 times a week.

If anyone has any suggestions, I would greatly appreciate them!

Thank you in advance!
Allison

 

[Ron Rosenfeld]

Hello!

I am having problems getting Excel to sum a column in a series of
spreadsheets. The information comes from a SQL query and is imported into
Excel. The problem is in column F, rows 2-141. No matter how I format the
cells, I can't get a total. I have tried multiple suggestions found on the
newsgroup (trim and clean macros, edit/replace with holding the alt key and
typing 0160, etc.) and nothing works. The only way I can get it to total is
copy/paste cells 2-141 into Word, then copy them out of Word and paste back
over the same cells in Excel. It will calculate then, so something is being
stripped out and allowing the calculation.

If there were only a few spreadsheets to deal with, I would do the
copy/paste in and out of Word each time. Unfortunately, there are 200
spreadsheets a week that are run and having this problem. I can't keep doing
the copy/paste in and out of Word 200 times a week.

If anyone has any suggestions, I would greatly appreciate them!

Thank you in advance!
Allison

Most likely, the cells are text. They may or may not have a nbsp (char(160))
in them.

Changing the format after importing them will not make a difference.

Several solutions:

If all you need to do is SUM the column, you could try the simpler of one of
these formulas that work, instead:

=SUMPRODUCT(--F2:F141)

=SUMPRODUCT(--(SUBSTITUTE(F2:F141,CHAR(160),"")))

If you really need to convert them, after doing the find/replace <alt160> with
nothing; enter a 1 in some cell; then EDIT/COPY; then select F2:F141; then
EDIT/PASTE SPECIAL/Multiply

--ron