Conditional formating on text contents

[Brad]

Can Excel 2002 use conditional formatting on partial text content? For
example, if I have a list of names, can it highlight cells with the value of
"Tom" if the list contains "Tom Smith, Tom Jones, George, Brown, etc?" Can
2007 do this? I am not on the computer with 2007 today, but could move if
the function is available only in 2007.

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64

 

[Bob Phillips]

Yes, uses a CF formula of

=ISNUMBER(SEARCH("tom",A1))

works in any version

 

[Dave Peterson]

Sure.

You have a few options -- with A1 the cell in question, you can use "Formula is"

=countif(a1,"*tom*")>0
or
=isnumber(search("tom",a1))
And since excel will treat an error as false, you could simplify the expression
to:
=search("tom",a1)

And if you want a case sensitive version:

=Find("tom",a1)

(=countif() is not case sensitive)

 

[T. Valko]

Can Excel 2002 use conditional formatting

on partial text content?

Conditional Formatting
Formula Is

=SEARCH("Tom",A1)

 

[Brad]

Thanks everyone.

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64