Find value between quotation mark in a cell

[olympiac]

Scenario:

If the sentence between quotation mark in "cell $A$1" is contained in the sentence between quotation mark in "cell B1" then display in "cell C1" the value "Yes" otherwise display the value "No"
Eg:
A1: "software code32"
B1: "software code32" "RMR software" "QWER system"
C1: Yes

or
A1: "software code32" "RMR software"
B1: "software code32"
C1: Yes


PS: I can change the quotation mark with anything else if it's necessary

 

[RichardMGreen]

Try something along the lines of
=IF(ISERROR(FIND(A1,B1)),"No","Yes")
in C3

 

[olympiac]

I tried your formula but it doesn't work properly. See scenario below where the result is cell C1 is "No" while the value ""QWER system" is in Cell B1 and as a result should give "Yes" in cell C1

A1: "new value" "QWER system"
B1: "software code32" "RMR software" "QWER system"
C1: No

Any idea?

 

[RichardMGreen]

That's because it's looking for the entirety of A1 in cell B1.
If you're looking for a fuzzy lookup (i.e. part of a cell in another cell), you'll probably need code of some sort.

 

[Aladin Akyurek]

Scenario:

If the sentence between quotation mark in "cell $A$1" is contained in the sentence between quotation mark in "cell B1" then display in "cell C1" the value "Yes" otherwise display the value "No"
Eg:
A1: "software code32"
B1: "software code32" "RMR software" "QWER system"
C1: Yes

or
A1: "software code32" "RMR software"
B1: "software code32"
C1: Yes


PS: I can change the quotation mark with anything else if it's necessary

Try...

=IF(ISNUMBER(LOOKUP(9.999999999999999E+307,CHOOSE({1,2},SEARCH(A1,B1),SEARCH(B1,A1)))),"Yes","No")

 

[RichardMGreen]

Hi Aladin - can you explain that one in stages please?

 

[Aladin Akyurek]

Hi Aladin - can you explain that one in stages please?

In

=IF(ISNUMBER(LOOKUP(9.999999999999999E+307,CHOOSE({1,2},SEARCH(A1,B1),SEARCH(B1,A1)))),"Yes","No")

A) SEARCH returns a position indicating integer.

SEARCH("JAD","KAD JAD") would give" 5.

SEARCH("KAD JAD","JAD") would give #VALUE!.

B) CHOOSE({1,2},Expression1,Expression2)

yields an array that consists of two evaluations. So:

CHOOSE({1,2},SEARCH("JAD","KAD JAD"),SEARCH("KAD JAD","JAD"))

yields:

{5,#VALUE!}

C) LOOKUP with 9.99999999999999E+307 as look up value returns the last numeric value if that exists from a reference it's given. Thus:

LOOKUP(9.99999999999999E+307,CHOOSE({1,2},SEARCH("JAD","KAD JAD"),SEARCH("KAD JAD","JAD")))

==>

LOOKUP(9.99999999999999E+307,{5,#VALUE!})

==>

5

D) ISNUMBER tests whether LOOKUP turns up a number. IF maps the test result onto Yes/No.

Hope this helps.

Note. For 9.99999999999999E+307 (or 9.99E+307 if so desired), see:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)

 

[RichardMGreen]

Thanks for that.
So by the looks of it, you can add as many options as you want to choose.
Is that right?

 

[pgc01]

Hi

Still not clear to me. With your last example it seems that if any of the sentences in quotation marks in A1 are present in B1, then you want Yes.

For ex:

A1: "a" "b" "c" "d"
B1: "e" "c" "f"

You want Yes because "c" (one of the sentences in quotation marks in A1) is also present in B1.

Is this correct?

 

[Aladin Akyurek]

Thanks for that.
So by the looks of it, you can add as many options as you want to choose.
Is that right?

Check the "limits" section in the Help bit of your Excel version. On Excel 2003 and earlier systems, it is 30.