# Thread: Whether cells contains specific text or not?

1. ## Whether cells contains specific text or not?

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

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

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

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

Originally Posted by 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.

Originally Posted by 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.

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

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

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

Originally Posted by 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.

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

Originally Posted by 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.

10. Originally Posted by 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.
I love it when I stand corrected!

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•