conditional formatting "if part of cell contents contains string"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

Given the following table (actually a very long one):

column A is:
A
1 Evergreen/Italia Marittima/Hatsu Marine
2 Evergreen/Italia Marittima/Hatsu Marine
3 Gold-Star-Zim, Emirates, SCI, MacAndrews

column B is:
B
1 HAM, RTM, THP, TAR, Port Said, TPP, KHH, NBO, SHA, YTN, HKG, TPP...
2 HAM, THP, ZEE, Port Said, CMB, TPP, KHH, YTN, HKG, OSA, TOK...
3 NYJ, PMH, CHS, BCN, CMB, TUT, JNP, MUN, BCN, NYJ.

How can I highlight cells that contain "Port Said"?

I presume conditional formatting, and the conditions will be different for
Column A and column B?

I know how to use conditional formatting is a cell has to be bigger than,
exact, contain exacttly a certain string etc, but in this case it's about
containing a certain string, beside other strings..

Thanks in advance!

tom
 
One way to play it ..

Assume the string input will be made in say, C1
viz input in C1: Port Said

Select col B
Click Format > Conditional Formatting
Under Condition 1, make the settings:
Formula is:
=SUMPRODUCT((ISNUMBER(SEARCH($C$1,B1)))*($C$1<>""))>0
Click Format > Patterns tab > Green? > OK
Click OK at the main dialog

The above will trigger green fills for cells in col B containing the input
string in C1. If you need it to be a stricter, case sensitive search, just
replace SEARCH with FIND in the CF formula. SEARCH is not case sensitive.
 
Back
Top