Sum does not work with numeric database Varchar values.

Question

I'm placing data queried from a database into excel cells. The data is Varchar, but numeric. The cells are formatted as general, but I still cannot sum several numeric values. I assume this is because the data is Varchar. Still if I add two cells it works fine. Is there any way to get sum to work? BTW I need to do this programatically. I don't want my users having to cut and paste or something like that.

Answer 1

Thank you all for your very quick responses.

Let me clarify a few things. The data looks like this in the formula box

135194.50

There are no quotes or anything.

As I mentioned, for my users sakes, I'd like to avoid pasting or creating formulas in additional columns, though I might have to go that route. I was hoping there was a setting somewhere. What baffels me is a formula like =A1+A2 works just fine, but =Sum(A1:A2) returns 0. This seems to be true whenever a number is put into a cell previously formatted as text. I note, however, that if you put a number in a cell and then reformat the cell as text, Sum works just fine. What is up with that?

Ok so now I'm just venting. Thanks again.

Answer 2

... but I still cannot sum several numeric values ...

Without seeing some sample data, it isn't easy guessing what is forcing the values to be treated as textual representations of numbers as opposed to actual numbers. This formula will compensate for a few of the more common errant characters:

=SUMPRODUCT(--CLEAN(SUBSTITUTE(A1:A99,CHAR(160),)))

... I assume this is because the data is Varchar .. I don't want my users having to cut and paste or something like that.

Ultimately, it would be better to clean up the data within the T-SQL export statement using CAST(...) or CONVERT(..) if you want to avoid end-user problems (see GIGO). If you're not using T-SQl, consult the appropriate language reference for equivalents.

Answer 3

have you checked for "." instead of "," or vise-versa for all numbers?

Answer 4

Format the cells as General, and then in a blank cell also formatted as General, enter the number 1. Copy that cell, select your Varchar data, right-click and select "Past Special" and select Values and Multiply and finish up.

Answer 5

I'm placing data queried from a database into excel cells. The data is Varchar, but numeric. The cells are formatted as general, but I still cannot sum several numeric values. I assume this is because the data is Varchar. Still if I add two cells it works fine. Is there any way to get sum to work? BTW I need to do this programatically. I don't want my users having to cut and paste or something like that.

Hi,

Even if you format the cells into General, the pasted data are still in Text format with out doing anything.

Let say the imported list is in AColumn starting in A1. So put this formula in B1...

=VALUE(A1)

Copy B1 and paste it downward.

Then you can do the Sum from BColumn.

How it works?

Jaeson