HELP: Conditional Formatting Based On A Sub String

[Hardeep Rakhra]

Hello,

I need some help on trying to apply some conditional formatting based on
sub string within string contained within a given cell.

For example;

CELL A1 contains "cat.dog.elephant.parrot.girrafe.duck"

I want the cell to be Green if the string contains the sub string "dog".

I haven't a clue how to archive this. Looking at the excel functions i
don't see a InStr.

Any ideas?

Hardeep.

 

[Ron Rosenfeld]

Hello,

I need some help on trying to apply some conditional formatting based on
sub string within string contained within a given cell.

For example;

CELL A1 contains "cat.dog.elephant.parrot.girrafe.duck"

I want the cell to be Green if the string contains the sub string "dog".

I haven't a clue how to archive this. Looking at the excel functions i
don't see a InStr.

Any ideas?

Hardeep.

Formula Is: =countif(a1,"*dog*")>0


--ron

 

[Cutter]

In the conditional format box change the "Cell value is" to "Formula is
and type this formula:
=search("dog",A1)>0

then set your format to green

 

[Hardeep Rakhra]

Hardeep:

The only problem with a SEARCH() or FIND() function that Cutter proposed is
that it returns an error if the substring is not found at all in the main
string. Ron's solution with COUNTIF() will not return an error, but is a
bit more tricky. You can use it if you concatenate "*" characters to the
front and back of the substring. For example,

=COUNTIF(A1, "*"&B1&"*") will give you 1 if B1 contains "dog" and A1
contains "cat.dog.rat"
=COUNTIF(A1, "*"&B1&"*") will give you 0 if B1 contains "goose" and A1
contains "cat.dog.rat"

Thank you Ed! I'll give that a go. Thanks Ron, and Cutter also for
yourhelp :O)

Hardeep.