Search for exact word in comma separated string

[DarkSmile]

Hi,

I'm trying to detect if a exact word is in a comma separated sting, but I'm getting false positives with my formula.

Let me give a bit of explanation, in B4 I have a dropdown list, where you can choose multiple categories , these categories get separated by a comma in the cell.
I now want to return true/false if the exact word is found in the cell.

TCA berekening.xlsm
	A	B
4	Categorieën:	CI, D, AII
5	FTE:
6
7	AI	yes
8	AII	yes
9	BI	no
10	BII	no
11	BIII	no
12	C0	no
13	CI	yes
14	CII	no
15	CIII	no
16	CIV	no
17	D	yes
18	E	no
19	FI	no
20	FII	no
21	G	no
22	H	no
23	I	yes
24	J	no
25	K	no
FSSC 22000 v6

Cell Formulas
Range	Formula
B7:B25	B7	=IF(ISNUMBER(SEARCH(A7,$B$4)),"yes","no")

Named Ranges
Name	Refers To	Cells
FSSC_CAT_DROPDOWN	='FSSC 22000 v6'!$B$4	B7:B25

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=FSSC_Cat
B5	List	=FSSC_FTE

 

[Fluff]

How about

=IF(ISNUMBER(SEARCH(","&A7&",",","&$B$4&",")),"yes","no")

 

[DarkSmile]

How about

=IF(ISNUMBER(SEARCH(","&A7&",",","&$B$4&",")),"yes","no")

Hi Fluff, thank you for your response, but it's not working, see below the output.

TCA berekening.xlsm
	A	B
4	Categorieën:	CI, D, AII
5	FTE:
6
7	AI	no
8	AII	no
9	BI	no
10	BII	no
11	BIII	no
12	C0	no
13	CI	yes
14	CII	no
15	CIII	no
16	CIV	no
17	D	no
18	E	no
19	FI	no
20	FII	no
21	G	no
22	H	no
23	I	no
24	J	no
25	K	no
FSSC 22000 v6

Cell Formulas
Range	Formula
B7:B25	B7	=IF(ISNUMBER(SEARCH(","&A7&",",","&$B$4&",")),"yes","no")

Named Ranges
Name	Refers To	Cells
FSSC_CAT_DROPDOWN	='FSSC 22000 v6'!$B$4	B7:B25

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=FSSC_Cat
B5	List	=FSSC_FTE

 

[Anonymous1378]

Perhaps add a space after the comma concatenated before the text strings?

Book4
	A	B
4	Categorieën:	CI, D, AII
5	FTE:
6
7	AI	no
8	AII	yes
9	BI	no
10	BII	no
11	BIII	no
12	C0	no
13	CI	yes
14	CII	no
15	CIII	no
16	CIV	no
17	D	yes
18	E	no
19	FI	no
20	FII	no
21	G	no
22	H	no
23	I	no
24	J	no
25	K	no
Sheet1

Cell Formulas
Range	Formula
B7:B25	B7	=IF(ISNUMBER(SEARCH(", "&A7&",",", "&$B$4&",")),"yes","no")

 

[DarkSmile]

Perhaps add a space after the comma concatenated before the text strings?

Book4
	A	B
4	Categorieën:	CI, D, AII
5	FTE:
6
7	AI	no
8	AII	yes
9	BI	no
10	BII	no
11	BIII	no
12	C0	no
13	CI	yes
14	CII	no
15	CIII	no
16	CIV	no
17	D	yes
18	E	no
19	FI	no
20	FII	no
21	G	no
22	H	no
23	I	no
24	J	no
25	K	no
Sheet1

Cell Formulas
Range	Formula
B7:B25	B7	=IF(ISNUMBER(SEARCH(", "&A7&",",", "&$B$4&",")),"yes","no")

worked thank you !