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

Replacing characters in numeric text strings and SUMming
 

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" wrote in message
ups.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

Replacing characters in numeric text strings and SUMming
 

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

Replacing characters in numeric text strings and SUMming
 

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

Thanks a lot -- much appreciated.

Replacing characters in numeric text strings and SUMming
 

Thanks also -- good to know!

Replacing characters in numeric text strings and SUMming
 

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.

Replacing characters in numeric text strings and SUMming
 

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.

Replacing characters in numeric text strings and SUMming
 

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.