SUM problem: adding formula results

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!

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

"JPN5804" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> JPN5804
> ------------------------------------------------------------------------
> JPN5804's Profile:
> http://www.excelforum.com/member.php...o&userid=29137
> View this thread: http://www.excelforum.com/showthread...hreadid=488557
>

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