Conditional Formatting with exact word anywhere in cell

[lightningwolf93]

Hey guys,

I am trying to use conditional formatting to identify when a certain word appears in a list - "Road". I used "specific text/contains" in conditional formatting, to highlight those cells.

However, it is also highlighting words like "Broad" (B-road).

Example- in the below, I want only the second line to be highlighted, but I am getting the 1st and 2nd line highlighted

123 Broad Street
547 Dury Road
789 Madison Avenue


Any advice would be appreciated!

 

[yky]

Try using the following rule:

=IF(FIND("Road",A1),TRUE,)

 

[lightningwolf93]

Hello yky,

Thanks for helping out! As a note, the data in my actual-list is not case sensitive (sometimes it is 'Road', sometimes "ROAD" etc.) I would like it to highlight 'Road' no matter what case it is in.


I go to 'Edit Formatting Rule' and then choose 'Use a formula to determine which cells to format'

in the formula bar, I type "IF(SEARCH("Road",A1),TRUE,FALSE)" - Both 'Road' and 'Broad' end up highlighted, as well as ROAD, BROAD etc.

in the formula bar, I type "IF(FIND("ROAD",A1),TRUE,FALSE)" - Both ROAD and BROAD end up highlighted, but Road and Broad are NOT highlighted.

Is there a way around this? So the cell is highlighted if it contains Road, ROAD etc but not BROAD,Broad

(As an additional note, I am using this as part of a larger macro I am recording, so if possible I would like to avoid adding a new column with all caps).

 

[yky]

In that case, try:

=IF(SEARCH(" road",A1),TRUE,)

I assume there is always a space before "road".

 

[lightningwolf93]

So simple! But it worked. Thank you

To add to the issue though, I tried to replicate the solution to fit other words. The column I have contains a list of addresses and I want to highlight the ones that don't follow our postal convention (so I want Road, Avenue, Street, Park, Boulvevard etc. to all be highlighted, because they should be Rd, Ave,St,Blvd instead).

Suppose I had

123 Parkedale Av
567 Green Park

I want only the second line to be highlighted. However, there is no space at the end of "Park" .

(I'm trying to use this as part of a macro, validating every list of addresses we receive, so I don't have a single, final list of all the addresses. It's an iterative process)

Is there any other solution you can think of?

 

[yky]

TRY:

=IF(OR(SEARCH("AVENUE",A1),SEARCH("PARK",A1),SEARCH("STREET",A1),SEARCH("BOULVEVARD",A1)),TRUE,)

But, "Park Av" will be highlighted, even though it shouldn't be and I can't find a way out of it.