SUM problem: adding formula results

J

JPN5804

I had several thousand cells with "0 true", "1 true", "1 false", and "0
false".

I finally removed all text using the LEFT function, leaving just 0 or 1
in each cell. I need to sum the rows now, and of course I couldn't do it
right away, I figured that I could "paste special", then check "values".


Here's the problem: I still can't calculate a sum. The 1's and 0's are
on the left side of the cell. If I simply type a 1 or 0 in the cell,
replacing the 1 or 0 already there, it shows up on the right of the
cell, and then it will sum. But obviously, I don't want to do this to
thousands of cells, I need to quickly convert these LEFT formula
results to values that will sum, but "paste special", "values" won't
work.

Thanks!
 
B

Biff

Hi!

The value returned by the LEFT formula is TEXT.

Either:

=LEFT(A1,1)*1
=--LEFT(A1,1)

Will convert the returned values to numeric numbers.

If you have already converted the formulas to constants by doing a
copy/paste special/values, try this:

Select an empty cell.
Goto Edit>Copy
Select the range of "numbers" in question.
Goto Edit>Paste Special>Add>OK

That should convert them into numeric numbers.

Biff
 
J

JPN5804

Thanks, paste special, add worked. I did not think that the formul
would yield a TEXT value
 
Top