Thanks for the solutions guys

I'm now off to study arrays

"Ron Rosenfeld" wrote:

On Mon, 10 Aug 2009 09:43:02 -0700, NCCADM
wrote:

Say cell A1 contains 1*, and A2 contains 1, when I do sum(A1:A2) I get 1 as
the answer (obviously).

I would like to be able to include the numerical part of the entries in
these cells and ignore the text part. I know that I can format cell A1 like
this: # "*", but to apply a special format to each cell that includes the *
is a clumsy approach I think.

Is there any way to format all the cells so that the *'s are ignored,
without writing a macro?

Thanks for your time.


=SUMPRODUCT(--SUBSTITUTE("0"&A1:A10,"*",""))

--ron