Is there anyway that I can add two cells ie.

cell 1 - (5 - Oranges)
cell 2 - (6 - Grapes)

and get a sum of 11 in the third cell...i cant just use the sum
function because of the text being in the cells

Try this:

=SUMPRODUCT(MID(A1:A2,2,FIND(" ",A1:A2)-1)+0)


" wrote:

Is there anyway that I can add two cells ie.

cell 1 - (5 - Oranges)
cell 2 - (6 - Grapes)

and get a sum of 11 in the third cell...i cant just use the sum
function because of the text being in the cells

Assuming every cell has an entry in the format: (number - text)

=SUMPRODUCT(--LEFT(SUBSTITUTE(A1:A2,"(",""),FIND(" ",A1:A2)-1))

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
Is there anyway that I can add two cells ie.

cell 1 - (5 - Oranges)
cell 2 - (6 - Grapes)

and get a sum of 11 in the third cell...i cant just use the sum
function because of the text being in the cells

Hi,

You can use the sum function as follows to solve your problem:

=SUM((VALUE(LEFT(A1,1))),VALUE(LEFT(A2,1)))

cell a1 -- 5 - oranges
cell a2 -- 6 - Grapes

Hope this will clear your doubt.

Regards,
Soundar.

" wrote:

Is there anyway that I can add two cells ie.

cell 1 - (5 - Oranges)
cell 2 - (6 - Grapes)

and get a sum of 11 in the third cell...i cant just use the sum
function because of the text being in the cells

Maybe approaching this from the other end is easier.

With just the digit 5 in cell A1, you can display

(5 - Oranges)

with this formula

="("&text(A1,"#")&" - Oranges)"

Cell A1 is still a number that can be acted upon with all the Excel operators


" wrote:

Is there anyway that I can add two cells ie.

cell 1 - (5 - Oranges)
cell 2 - (6 - Grapes)

and get a sum of 11 in the third cell...i cant just use the sum
function because of the text being in the cells

On Oct 12, 1:45 pm, Soundar wrote:
Hi,

You can use the sum function as follows to solve your problem:

=SUM((VALUE(LEFT(A1,1))),VALUE(LEFT(A2,1)))

cell a1 -- 5 - oranges
cell a2 -- 6 - Grapes

Hope this will clear your doubt.

Regards,
Soundar.



" wrote:
Is there anyway that I can add two cells ie.

cell 1 - (5 - Oranges)
cell 2 - (6 - Grapes)

and get a sum of 11 in the third cell...i cant just use the sum
function because of the text being in the cells- Hide quoted text -

- Show quoted text -

Well...quite the amount of answers in the short time and I would like
to say thanks

=SUM((VALUE(LEFT(A1,1))),VALUE(LEFT(A2,1)))

this formula works great for what im trying to do, but i now run into
another problem of say I am trying to do this for cells A1 through
A100 is there a way I can specify that without having to type
VALUE(LEFT for every cell?

wrote in message
oups.com...
On Oct 12, 1:45 pm, Soundar wrote:
Hi,

You can use the sum function as follows to solve your problem:

=SUM((VALUE(LEFT(A1,1))),VALUE(LEFT(A2,1)))

cell a1 -- 5 - oranges
cell a2 -- 6 - Grapes

Hope this will clear your doubt.

Regards,
Soundar.



" wrote:
Is there anyway that I can add two cells ie.

cell 1 - (5 - Oranges)
cell 2 - (6 - Grapes)

and get a sum of 11 in the third cell...i cant just use the sum
function because of the text being in the cells- Hide quoted text -

- Show quoted text -

Well...quite the amount of answers in the short time and I would like
to say thanks

=SUM((VALUE(LEFT(A1,1))),VALUE(LEFT(A2,1)))

this formula works great for what im trying to do, but i now run into
another problem of say I am trying to do this for cells A1 through
A100 is there a way I can specify that without having to type
VALUE(LEFT for every cell?


Try one of the other suggestions.

--
Biff
Microsoft Excel MVP