Whether cells contains specific text or not?

[vinay]

Hello Everyone!
I have a question to put on this forum.
e.g.

A1 = "cat 5 dog 8"
A2 = "rat 3 bat 7"
A3 = "hat 2 mat 9"

and here is my question
I want a formula that can return the number next to the specified text.
like
in B1 = if A1:A3 contains "dog" then the value should be 8
in B2 = if A1:A3 contains "hat" then the value should be 2
in B3 = if A1:A3 contains "cat" then the value should be 5


Waiting for your early replies...

[Ken Puls]

Does your data have to be in that form, or could you put it in a table like this:

A1 = "cat"
B1 = "5"
A2 = "dog"
B2 = "8"
etc...

Reason I ask is that getting the info you want at that point will be VERY easy. Trying to do what you're asking is going to be difficult.

[JoePublic]

Assuming B1:B3 contain dog, hat and cat respectively, then in C1:
=LOOKUP(1E+100,IF(ISNUMBER(SEARCH(B1,$A$1:$A$3)),-LOOKUP(2,-MID($A$1:$A$3,SEARCH(B1,$A$1:$A$3)+LEN(B1)+1,ROW($1:$255))),""))
array-entered (with Ctrl+Shift+Enter) and copy down to C3.

[vinay]

No, My data is in the form said by me above not like you.
e.g. A1 contains cat 5 dog 8 --------> {cat} space {number} space {dog} space {number}
A2 contains rat 3 bat 7 ---------> {rat} space {number} space {dog} space {number}
and Onwards

Does your data have to be in that form, or could you put it in a table like this:

A1 = "cat"
B1 = "5"
A2 = "dog"
B2 = "8"
etc...

Reason I ask is that getting the info you want at that point will be VERY easy. Trying to do what you're asking is going to be difficult.

[vinay]

Thanks Joe for your efforts.. but your assumption about my data formation is wrong. it is like my above reply.

Assuming B1:B3 contain dog, hat and cat respectively, then in C1:
=LOOKUP(1E+100,IF(ISNUMBER(SEARCH(B1,$A$1:$A$3)),-LOOKUP(2,-MID($A$1:$A$3,SEARCH(B1,$A$1:$A$3)+LEN(B1)+1,ROW($1:$255))),""))
array-entered (with Ctrl+Shift+Enter) and copy down to C3.

[Ken Puls]

Right, we understand how your data is. What we're asking is if it can be changed, or if you're bound to that format.

The challenge we have is that Excel is built to work with different data in different columns. It loves tables, and doesn't really like having strings of data in one block to sort through. If you were looking for a value in one cell, it would be easy, but by trying to expand this over multiple cells it makes it very dificult. Joe's formula will work fine if your data is split, but array formulas (to deal with multiple conditions) don't support text functions, which we'd need to pull this off.

The only way I can see to do this would be to write a custom function in VBA. Can certainly be done, but honestly, if you can convert your data to a more comlpiant format, it's going to be faster to develop and WAY easier to maintain.

[JoePublic]

I think you misunderstood my formula. If you want hardcoded values, replace the B1 cell reference with literal text. It works on your data as is.

[vinay]

No, I am bound to that format. I think we can split that data in to columns by using functions and then make appropriate formula..

Right, we understand how your data is. What we're asking is if it can be changed, or if you're bound to that format.

The challenge we have is that Excel is built to work with different data in different columns. It loves tables, and doesn't really like having strings of data in one block to sort through. If you were looking for a value in one cell, it would be easy, but by trying to expand this over multiple cells it makes it very dificult. Joe's formula will work fine if your data is split, but array formulas (to deal with multiple conditions) don't support text functions, which we'd need to pull this off.

The only way I can see to do this would be to write a custom function in VBA. Can certainly be done, but honestly, if you can convert your data to a more comlpiant format, it's going to be faster to develop and WAY easier to maintain.

[vinay]

WHOA..!!! You made it Joy..
I am full of Joy now.. Hehee..
WAIT......
A problem is still there in this formula.
e.g.
A1 = Dog 15 cat 8

this formula returns value of Dog with 1 instead of 15..
Hope you find out the solutions ASAP.
and Extremely Thankful to you for your assistence.

Assuming B1:B3 contain dog, hat and cat respectively, then in C1:
=LOOKUP(1E+100,IF(ISNUMBER(SEARCH(B1,$A$1:$A$3)),-LOOKUP(2,-MID($A$1:$A$3,SEARCH(B1,$A$1:$A$3)+LEN(B1)+1,ROW($1:$255))),""))
array-entered (with Ctrl+Shift+Enter) and copy down to C3.

[Ken Puls]

I think you misunderstood my formula. If you want hardcoded values, replace the B1 cell reference with literal text. It works on your data as is.

I love it when I stand corrected!