Adding Cells that Contain Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

derr04@gmail.com - 12 Oct 2007 18:29 GMT

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

Reply to this Message

Teethless mama - 12 Oct 2007 18:43 GMT

Try this:

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

> Is there anyway that I can add two cells ie.
>
[quoted text clipped - 3 lines]
> 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

Reply to this Message

T. Valko - 12 Oct 2007 18:43 GMT

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

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

Signature

Biff
Microsoft Excel MVP

> Is there anyway that I can add two cells ie.
>
[quoted text clipped - 3 lines]
> 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

Reply to this Message

Soundar - 12 Oct 2007 18:45 GMT

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.

> Is there anyway that I can add two cells ie.
>
[quoted text clipped - 3 lines]
> 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

Reply to this Message

derr04@gmail.com - 12 Oct 2007 18:59 GMT

> Hi,
>
[quoted text clipped - 20 lines]
>
> - 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?

Reply to this Message

T. Valko - 12 Oct 2007 23:21 GMT

>> Hi,
>>
[quoted text clipped - 30 lines]
> A100 is there a way I can specify that without having to type
> VALUE(LEFT for every cell?

Try one of the other suggestions.

Signature

Biff
Microsoft Excel MVP

Reply to this Message

Duke Carey - 12 Oct 2007 18:45 GMT

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

> Is there anyway that I can add two cells ie.
>
[quoted text clipped - 3 lines]
> 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

Reply to this Message