Replacing characters in numeric text strings and SUMming

Hi,

Can anybody help me SUM the following two numbers *without* changing
the orginal cell contents? Excel sees them as text and the second
number is a minus number. I'd rather do this with regular formulae than
VBA, but I'll settle for either


Thanks a mill.


$1,418.26
($2,189.74)
---------------
-771.48

If there are no other invisible characters involved

=SUM(--(A1:A2))

entered with ctrl + shift & enter

or

=SUMPRODUCT(--(A1:A2))

entered normally

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"rkd" <ross.k.davis@gmail.com> wrote in message
news:1145453832.485169.39640@z34g2000cwc.googlegroups.com...
>
> Hi,
>
> Can anybody help me SUM the following two numbers *without* changing
> the orginal cell contents? Excel sees them as text and the second
> number is a minus number. I'd rather do this with regular formulae than
> VBA, but I'll settle for either
>
>
> Thanks a mill.
>
>
> $1,418.26
> ($2,189.74)
> ---------------
> -771.48
>

Or, as an alternative,
=VALUE(A1)+VALUE(A2)

That was easier than I expected -- ended up using SUMPRODUCT!

Thanks a lot -- much appreciated.

Thanks also -- good to know!

Actually, without thinking I used the formula against the numbers I did
a Find/Replace on which worked fine but
when I tried it against the numbers in example format I'm getting
'#VALUE!' errors -- any ideas?


Thanks.

The dollar and parentheses are part of the text strings in the example,
so from what I can tell they need to be stripped
out and the numbers in parentheses converted to negative before the
formulae above will work!?

Also, as I said above, I need to keep the original cells as they are
with the dollar sign and parentheses.

thanks again.

Ok, I ended up doing this a rather unneat way but it works. I made an
out-of-sight column
corresponding to the original column with this formula:

=IF(ISTEXT(L6),--(IF(LEFT(L6,1)="(",-RIGHT(LEFT(L6,LEN(L6)-1),LEN(LEFT(L6,LEN(L6)-1))-2),RIGHT(L6,LEN(L6)-1))),)

This stripped out the the $,(,) characters and adds a '-' for negative
numbers in parentheses. Then I AutoSum the column and
referenced the result where I needed it.

If anybody knows a neater way I'd be interested to see/hear it.