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.

Assuming that every cell that contains "*" also contains a number...

Try this array formula** :

=SUM(IF(RIGHT(A1:A10)="*",--LEFT(A1:A10,LEN(A1:A10)-1)),A1:A10)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


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

Perhaps:

=SUM(IF(ISNUMBER(A1:A2),A1:A2,LEFT(A1:A2,FIND("*", A1:A2)-1)+0))

confirmed with CTRL+SHIFT+ENTER not just ENTER


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123976

To sum all cells; try the below formula

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=SUM(IF(A1:A20<"",SUBSTITUTE(A1:A20,"*","")+0))

If this post helps click Yes
---------------
Jacob Skaria


"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.

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

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