Conditional Formatting to highlight cells that include *text*

[Floyd1]

Hi guys,

I have a spreadsheet which contains a list of people (A new row for each person. Information on home country, organisation, contact number etc.. are in columns A-Z).

I want to highlight any cell in columns I, J and K which contain specific 'key words'. The column of key words is located on the same workbook but a different sheet called "Custom search" ( cells B2-B62).

So far, I have the following formula; =MATCH(I1,'Custom search'!$B$2:$B$62,0)

This formula works fine however it only highlights cells which contain one of the key words exactly. I need it to highlight any cell which contains one of the key words even if there is other text in the cell.

Thanks!

 

[Oscar Cronquist]

Try this formula:

=MATCH("*"&I1&"*",'Custom search'!$B$2:$B$62,0)

* - wildcard

 

[Floyd1]

Thanks Oscar,

I'm still having problems as it only recognises "*" as the actual character.

e.g. - If I am searching for "HAV" as my key word, your formula will highlight cells containing "HAV" and "*HAV*", but it wont recognise "the HAV is". For some reason it's not recognising the * as a wild card function?

So far I have tried;
=MATCH("*"&I1&"*",'Custom search'!$B$2:$B$62,0)
=MATCH(I1,"*"&'Custom search'!$B$2:$B$62&"*",0)

 

[Oscar Cronquist]

Try this:

=OR(ISNUMBER(SEARCH(I1,'Custom search'!$B$2:$B$62)))

 

[Floyd1]

Many thanks again Oscar.

Unfortunately, I am having the same issue with this formula =OR(ISNUMBER(SEARCH(I1,'Custom search'!$B$2:$B$62))).

I have also tried =OR(ISNUMBER(SEARCH("*"&I1&"*",'Custom search'!$B$2:$B$62))) but with no luck...

 

[Peter_SSs]

If there will be no blank cells in 'Custom search'B2:B62 then try this as your CF formula

=COUNT(SEARCH("*"&'Custom search'!$B$2:$B$5&"*",I1))


If there is, or could be, blank cells in that range then try

=COUNT(IF('Custom search'!$B$2:$B$62<>"",SEARCH("*"&'Custom search'!$B$2:$B$5&"*",I1)))



Edit: Actually, if you are looking to find whole "words" rather than just "text strings" then try

=COUNT(IF('Custom search'!$B$2:$B$62<>"",SEARCH("* "&'Custom search'!$B$2:$B$5&" *"," "&I1&" ")))

 

[Floyd1]

Hi Peter,

Thanks for your suggestion! There is no blanks in 'Custom search'B2:B62 however neither =COUNT(SEARCH("*"&'Custom search'!$B$2:$B$5&"*",I1)) or =COUNT(IF('Custom search'!$B$2:$B$62<>"",SEARCH("* "&'Custom search'!$B$2:$B$5&" *"," "&I1&" "))) highlighted any cells containing the key words

I have found a work around though - I have set up a reference sheet ("Custom search ref sheet") with =COUNT(SEARCH('Custom search'!$B$2:$B$62,'Master list'!I3))>0 for each cell I want to search (the first cell being I3). I then use conditional formatting formula ='Custom search ref sheet'!B2 to highlight the cells containing any of my key words. This seems to have worked for me.

Thanks for all your help everyone!

 

[Peter_SSs]

A few comments.

1. Must have been too tired when I made post #6 as all those formulas were (slightly) more complex than needed.

2. It is fine if you want to stick with your work-around, but this is definitely doable without the extra sheet.

3. I suspect part of the problem might be related to row references. You say that row 3 is the first row to check but all the earlier formulas in the thread referred to I1. Even your current work-around seems a bit unusual to me in that your CF formula for I3 refers to B2 on 'Custom search ref sheet'. I suspect there would be less confusion if the formulas on that sheet were all moved down 1 row so that I3 on 'Master List' looked at B3 on that sheet etc.

4. I'm not suggesting that you should change it, but your formula in 'Custom search ref sheet' does not require the red part below. If it didn't have that, the formula would return a number: 0 if there are no matches and a positive integer if there is any matches. Conditional Formatting evaluates 0 as FALSE and any other number as TRUE so it would work just as well.
=COUNT(SEARCH('Custom search'!$B$2:$B$62,'Master List'!I3))>0

5. From your work-around, it appears that you are not looking for whole words. For example, if 'Custom search' contains "red" and 'Master List' contains "Anne was scared", your CF highlights that cell.

Anyway, here is my sample data, showing that the CF can work without the helper sheet.

Part of the reference list sheet:

Excel Workbook
	B
1
2	Word01
3	Word02
4	red
5	Word04
6	Word05
7	Word06
8	Word07
9	Word08
10	Word09
11	Word10
Custom search

Now Master List with CF formula shown, & highlighting the same cells that your work-around did when I tested that.

Excel Workbook
	I
3	This contains Word10 in it
4
5	Word01 is first
6	Ben ran home
7	Ann was scared
8
Master List

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3	1. / Formula is =COUNT(SEARCH('Custom search'!$B$2:$B$62,I3))	Abc