Excel search for multiple text strings in a cell

[weasel_girl]

Hi,

I have a data from a survey and I would like to look up whether a cell contains specific answer keys and then return an appropriate response.

In the example below, I want to look in the Data column, if the data cell contains "F1" AND "F2" AND "F3" then the response column would be "Yes", otherwise the response should be "No". I'm stuck on how to construct the AND statement to use with the search function. Help gratefully appreciated!

Data	Response
F1 - v56; F2 - v59; F3 - t66	Yes
F1 - v56; F2 - v59; F3 - t66	Yes
F1 - v56; F2 - v59	No
F3 - t66	No

 

[Kerryx]

Book1
	A	B	C
1	Data	Response
2	F1 - v56; F2 - v59; F3 - t66	Yes	Yes
3	F1 - v56; F2 - v59; F3 - t66	Yes	Yes
4	F1 - v56; F2 - v59	No	No
5	F3 - t66		No
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"F1","F2","F3"},A2)))=3,"Yes","No")

 

[mohadin]

How about

Sub test()
    Dim a As Variant: Dim i&
    a = Sheets("Sheet1").Range(Cells(1.1), Cells(1, 1).End(xlDown).Cells)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[(FFF)]."
        For i = 2 To UBound(a)
            If .Execute(a(i, 1)).Count = 3 Then:  a(i, 1) = "Yes": Else a(i, 1) = "NO"
        Next
    End With
    a(1, 1) = "Response"
    Cells(1, 2).Resize(UBound(a)) = a
End Sub

 

[Anthony47]

A possible option, with formula:
-in B1 insert the keys to be checked
-in B2 insert the formula

=LET(ckNum,SUBSTITUTE(SUBSTITUTE($A2,"-"," "),";"," ")&" ",mNum,B$1,mbits,TEXTSPLIT(mNum,," "),IF(SUM(--ISNUMBER(SEARCH(mbits&" ",ckNum)))=COUNTA(mbits),"Yesss","No"))

Copy to the right, for as many cells have been filled with keys
Then copy the first line of formulas down

Results as in the following example:

Cartel1
	A	B	C	D	E	F
1	Data	F1 F2 F3	F2 F1	F t66	C5	J3
2	F1 - v56; F2 - v5; F3 - t66	Yesss	Yesss	No	No	No
3	F1 - v56; F2 - v59; F3 - t66	Yesss	Yesss	No	No	No
4	F1 - v56; F2 - v59	No	Yesss	No	No	No
5	F3 - t66	No	No	No	No	No
6	FA C55 H2 J3	No	No	No	No	Yesss
7	F1 ZZ;F2;BB;C5	No	Yesss	No	Yesss	No
Foglio2

Cell Formulas
Range	Formula
B2:F7	B2	=LET(ckNum,SUBSTITUTE(SUBSTITUTE($A2,"-"," "),";"," ")&" ",mNum,B$1,mbits,TEXTSPLIT(mNum,," "),IF(SUM(--ISNUMBER(SEARCH(mbits&" ",ckNum)))=COUNTA(mbits),"Yesss","No"))

 

[weasel_girl]

Book1
	A	B	C
1	Data	Response
2	F1 - v56; F2 - v59; F3 - t66	Yes	Yes
3	F1 - v56; F2 - v59; F3 - t66	Yes	Yes
4	F1 - v56; F2 - v59	No	No
5	F3 - t66		No
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"F1","F2","F3"},A2)))=3,"Yes","No")

Thank you - this worked perfectly.

Thanks for the other suggestions too - I may need to extend this in the future so it will be very useful.

 

[Kerryx]

Glad to help

 

[Peter_SSs]

Another short formula option would be

22 09 13.xlsm
	A	B
1	Data	Response
2	F1 - v56; F2 - v59; F3 - t66	Yes
3	F1 - v56; F2 - v59; F3 - t66	Yes
4	F1 - v56; F2 - v59	No
5	F3 - t66	No
Yes No

Cell Formulas
Range	Formula
B2:B5	B2	=IF(COUNT(FIND({"F1","F2","F3"},A2))=3,"Yes","No")