PDA

View Full Version : Whether cells contains specific text or not?

vinay
2011-10-23, 09:00 PM
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
2011-10-24, 05:11 PM
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
2011-10-25, 09:46 AM
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
2011-10-25, 10:30 AM
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
2011-10-25, 10:32 AM

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
2011-10-25, 04:25 PM
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
2011-10-25, 04:54 PM
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
2011-10-25, 05:35 PM
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
2011-10-25, 05:45 PM
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
2011-10-25, 06:10 PM
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!

JoePublic
2011-10-25, 08:04 PM
Is it possible to rearrange your data or use VBA as Ken suggested? It will be simpler, I think, than any formula that will work with that layout.

vinay
2011-10-25, 08:15 PM
I dont have VBA and I dont know how to operate this software...
Let it GO..!
I have a gud news,, YOur formula is returning right value even with two digits now..!!
So, my problem is sorted out.. I think.. ;)

Is it possible to rearrange your data or use VBA as Ken suggested? It will be simpler, I think, than any formula that will work with that layout.

Ken Puls
2011-10-26, 05:55 AM
I dont have VBA and I dont know how to operate this software...
Let it GO..!
Hey settle down now... you actually DO have VBA, it comes with Excel. And we help people implement it all the time. That's why we're here.

I have a gud news,, YOur formula is returning right value even with two digits now..!!
So, my problem is sorted out.. I think.. ;)