Conditional formatting

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hello Group -

I have a database that I need to search for words or
groups of words. I type in cell G1 the word(s) I need to
find. I use conditional formatting to highlight the cells
that contain those word(s). I use this formula:

=AND(ISNUMBER(SEARCH(G$1,B1)),G$1<>"")

This works OK except for one small glitch. If I enter in
G1 the word "AND" all cells that contain the string "AND"
are highlighted. eg: SAND, LAND, GRAND etc. How can I get
this to work on only the specific word "AND" and not the
string of letters "AND" ? I don't want to use filters, I
want to use CF.

Thanks
Bob P.
 
=AND(G$1<>"",OR(B1=G$1,LEFT(B1,LEN(G$1)+1)=G$1&" ",ISNUMBER(SEARCH(" "&G$1&"
",B1)),RIGHT(B1,LEN(G$1)+1)=" "&G$1))

or, in English:
if G1 is not empty
and
b1 is equal to g1
or b1 starts with g1 followed by a space
or b1 contains g1 with spaces each side
or b1 ends with a space followed by g1
 
Back
Top