Conditional Formatting - comparison with another cell

[Jezza]

I'm trying to get a cell highlighted if its value contains the (alphabetic)
value in another cell. I've got a rule in cell C16 of =B5 ="td" and this
correctly highlights when C16 and C5 are both set to 'td'.

But I want it to really to be 'contains' not '=' and then to be able to put
in a string of values 'td, ps, jc' and so on. So C16 could be set to 'JC/PS'
and would go red if C5 contained either 'JC' or 'PS'.

 

[Luke M]

First, do note that your first formula
=B5="td"

has no correlation to what is going on in C16. It only checks if B5 = "td".

As to your question, the CF formula is:
=ISNUMBER(SEARCH(C5,C16))

Note that if you want it to be case-sensitive, change SEARCH to FIND.

 

[Sean Timmons]

Try:

=IF(ISERROR(FIND("td",B5)),FALSE,TRUE)

 

[David Biddulph]

You don't need
=IF(...,FALSE,TRUE)

=NOT(ISERROR(FIND("td",B5))) should do the job,
or =ISNUMBER(FIND("td",B5))
or =ISNUMBER(SEARCH("td",B5)) if you want the search to be case insensitive
which the OP wanted.

 

[Sean Timmons]

Good tips. Thank you for the help!

You don't need
=IF(...,FALSE,TRUE)

=NOT(ISERROR(FIND("td",B5))) should do the job,
or =ISNUMBER(FIND("td",B5))
or =ISNUMBER(SEARCH("td",B5)) if you want the search to be case insensitive
which the OP wanted.
--
David Biddulph




.