Search Conditional formatting exact match

[JMWh1t3]

Hiya All,

I have the following formula set in Conditional formatting to highlight the entire row when the town Aston is present.

=SEARCH("Aston",$G1)

The issue I have is it also highlights cells that contain words with "aston" in them i.e. Glastonbury.

Is there a way to stop this?

Thanks in Advance!

 

[dreid1011]

What column contains the town(s)? Is there any other text/data in those cells with the town(s)?

 

[JMWh1t3]

What column contains the town(s)? Is there any other text/data in those cells with the town(s)?

Towns are in column G:G. and there is only towns in the column, but may have multiple towns in one cell formatted as follows "Town1 / Town2 / Town3 / Town4" anywhere from 1 to 6 towns.

 

[dreid1011]

Hm, well it seems the solution I was going to suggest is not allowed as a CF formula. Is it an option to use a helper column?

If it is an option, you can try this, then check the helper column for value greater than 0:

Book1
	F	G
1	1	ASTON / BIRCHFIELD / GIB HEATH
2	0	GASTONBURY
3	1	ASTON
Sheet2

Cell Formulas
Range	Formula
F1:F3	F1	=SUM(--BYCOL(TEXTSPLIT(G1," / "),LAMBDA(t,EXACT("ASTON",t))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1:G3	Expression	=$F1>0	text	NO

 

[JMWh1t3]

Hm, well it seems the solution I was going to suggest is not allowed as a CF formula. Is it an option to use a helper column?

If it is an option, you can try this, then check the helper column for value greater than 0:

Book1
	F	G
1	1	ASTON / BIRCHFIELD / GIB HEATH
2	0	GASTONBURY
3	1	ASTON
Sheet2

Cell Formulas
Range	Formula
F1:F3	F1	=SUM(--BYCOL(TEXTSPLIT(G1," / "),LAMBDA(t,EXACT("ASTON",t))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1:G3	Expression	=$F1>0	text	NO

I like this solution, but cannot use a helper column unfortunately.

 

[myall_blues]

Hiya All,

I have the following formula set in Conditional formatting to highlight the entire row when the town Aston is present.

=SEARCH("Aston",$G1)

The issue I have is it also highlights cells that contain words with "aston" in them i.e. Glastonbury.

Is there a way to stop this?

Thanks in Advance!

Replace SEARCH with FIND. FIND is case sensitive.

 

[RoryA]

If your delimiters are consistent:

=SEARCH(" / Aston / "," / "&$G1&" / ")

 

[JMWh1t3]

Replace SEARCH with FIND. FIND is case sensitive.

Perfect and simple option. Have never used FIND before. But I will add it to my arsenal. Thanks!

 

[myall_blues]

Glad we could help. Thanks for the feedback.