Conditional formatting duplicate words within different cells

[reconcomm]

Thanks for taking the time to read this.

Very novice to Excel compared to most. Thanks for your advice.

We use basic conditional formatting of duplicates often at my work. If two cells are exactly the same I know how to make them light up.

So my question is how can I get Excel to identify duplicates between two cells that are NOT exactly the same.

For instance one cell might contain [RB James Starks GB] and the other cell only [James Starks]. Can excel pick out the individual duplicate names and disregard the [Dr and GB] portion of the first cell?

Thanks.

 

[JoeMo]

Welcome to the board!

I'll assume you know how to use a formula to set the conditions for the format you want. Suppose your list is in A1:A100, then the CF formula is:

=AND(ISNUMBER(SEARCH("James Starks",$A1)),COUNTIF($A$1:$A100,"*James Starks*")>1)

EDIT: This is not case sensitive. If you want it to be case sensitive change the SEARCH function to a FIND function.