Excel Conditional Format Two Columns that Contain Text

[pkuck]

I am looking for a formula to compare two columns of text and highlight the cells that contain similar text. The lists are company names. Column A is full company name ABC Co. Inc, etc. sometimes containing paranethese and other junk. Column two is just the simple business name. ABC.

I want to highlight the cells that contain the text from B since its the more simple text. I've been able to highlight the exact matches, but not when column A contains but is not exact.

 

[SkipVought]

Hi,

assuming that the full co name is in col A and the short in col B, starting in row 2...
[tt]
=FIND($B2,$A2)>0
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pkuck]

Thanks. That was close but didn't quite work. I need it to check any of the text in any of the cells in one column with any text in any of the cells in the other column.

 

[Gavona]

1.Do you want to check for one value from B at a time? I assume not.
2.I assume "any of the text" means whole words delimited by spaces?

I would approach a different way:

Split B into one column per word using Data,TextToColumns

Combine all the resultant columns into a single long one with one word per cell (involves sorting then copy/paste).Assume this is column B

[at this stage there probably is a formula that would do the job you want but I would use advanced filter]

Create a new column C based on this: ="=*"&B2&"*" (untested)

Use column C as the criteria for advanced filter on column A

All rows containing one of those words will be displayed. Select and apply a colour. Data, Filter, ShowAll


Gavin