Conditional formatting base on another cell (if the cell 'contains' for direct match)

[Peptide]

Hello,
I know i can do exact match formatting using the conditional formatting function, Is this possible?

Data in A1 is selected from 10 options in a pick list (i.e 128 Leslie Road or 18 Taunton Street)
I would like B1 to be highlighted (yellow is fine) if A1 contains either the word Road or Ave.

Any thoughts?

Thanks in advance,
Bob

 

[Special-K99]

Select B1

Conditional Formatting
New Rule
Use a formula to determine...

=ISNUMBER(SEARCH(" Rd",A1))+ISNUMBER(SEARCH(" Ave",A1)+ISNUMBER(SEARCH(" Road",A1))+ISNUMBER(SEARCH(" Avenue",A1))

Make sure you include the space before "Rd and "Ave" or you'll get cells highlighted for things like

Bardon Close
Havelock Street

 

[Joe4]

Use this CF formula for cell B1:

=OR(ISNUMBER(FIND("Road",A1)),ISNUMBER(FIND("Ave.",A1)))

Note that "FIND" is case-sensitive. If you do not want it to be case-sensitive, use "SEARCH" instead of "FIND".

 

[Peptide]

Excellent and Thanks so much!