How to conditionally format cells which contain the same string of text?

Question

I am trying to change the colour of cells based on indivudual strings they contain rather than the entire value of the cell. Specifically, I want to format cells if they share the same string.

For example, if I have the following table:

Pair 1             Pair 2            Pair 3              Pair 4
Apples - Bananas   Oranges - Pears   Apples - Avocados   Kiwis - Watermelons

Then the cells containing 'Apples - Bananas' and 'Apples - Avocados' would be highlighted because they both contain the string 'Apples'.

I have tried everything I can think of from countifs to using the left/right functions. The best success I have had is with the following conditional formatting formula:

=LEFT(C$27, SEARCH("-", C$27, 1) -2) = LEFT($C$27:$E$27, SEARCH("-", $C$27:$E$27, 1)-2)

Where C$27 is the starting cell, $C$27:$E$27 is the range I want to format, The first LEFT and SEARCH function combination pulls the left most string in the cell and the second LEFT and SEARCH combination does the same but over the range. This works decently, but it always recognized the first cell in the range as having fulfilled the condition and bases the string to search for off of that value even over the rest of the range.

Answer 1

Use this formula in you Conditional formatting:

=SUMPRODUCT(--ISNUMBER(SEARCH("-"&TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1))-1)*999+1,999))&"-","-"&SUBSTITUTE($A$2:$D$2," ","")&"-")))>LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1

this applied to A2:D2

---

And to prove that it does not matter the order and that it is whole word specific(will not find the Apples in CrabApples)

Answer 2

Use the conditional formatting: