conditional formatting "if part of cell contents contains string"
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 |
conditional formatting "if part of cell contents contains string"
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tom ossieur" wrote: 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 exactly a certain string etc, but in this case it's about containing a certain string, beside other strings.. Thanks in advance! tom |
All times are GMT +1. The time now is 10:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com