Hi there im a trying to add conditional formatting when one cell contains partial text and the other contains anything other cell contains anything other than "Portsmouth".

I have used the following formula however C6 could contain a combination of letters or spaces after water which I want it still to consider.


=AND($C6="Water", $D6<>"Portsmouth")



So if C6 was to =

Water
Water Top
Waters
Water Bottom
Etc

It should still change colour.

I assumed I would need to use a SEARCH function but I couldn't get it to work with an AND.