Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 13th 07, 10:54 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 33
Default 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

  #2   Report Post  
Old March 13th 07, 11:11 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to increase size of "name box" and "contents of cell " displa. Stubby- LIBERTY New Users to Excel 2 February 22nd 07 06:43 PM
How do I reference a cell as PART of a vlookup "Table_Array" locat -Rocket Excel Worksheet Functions 2 January 4th 07 03:49 PM
get the VLOOKUP function to return the "contents" of a cell? sbellybutton Excel Worksheet Functions 8 October 3rd 06 03:27 AM
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell? [email protected] Excel Worksheet Functions 3 November 14th 05 01:48 AM
if one cell has "X" textin contents, then pull cell "Y"... azazel Excel Worksheet Functions 3 October 24th 05 05:43 PM


All times are GMT +1. The time now is 05:52 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017