Matching partial text to partial text - is this possible?

I have two columns with text and I want to match, row by row, any word in column A cell to any word in column B cell.

For example:

A1: The cat sat on the mat
B1: I have a dog and a budgie and a cat

I want a formula in cell C1 which pulls out the word cat as appearing in both A1 and B1, is this possible?

The real life spread sheet is actually lists of companies and I need to know if they fulfil two criteria however there are variable numbers of companies in each cell and they are often written differently, e.g. A1 = Tesco but B1 = TESCO PLC.

Any help gratefully received!

Thanks,
Samantha

From your description I think you will need to use VBA to solve this.

The SEARCH function will do what you want: the issue is determining the string (word) you want to search for as it would be necessary to find each word in one of the strings (A1) and compare to the second string (B1).

Using your example ....

=IF(AND(ISNUMBER(SEARCH("cat",A1)),ISNUMBER(SEARCH("CAT",B1))),"yes","no")

If cat/CAT is found in both strings, result is YES.

Plus points made by "Special-K".

Thanks very much guys, I think the VBA would be the only possible option then, how would I go about that?

If that doesn't work it looks like I'm back to the nightmare situation of checking 3000 rows manually! Although that would probably be just as quick as snipping out all the possible prefixes and suffixes as many of them are listed companies with their unique stock market ticker ID next to them.

Its a one off research task not a recurring thing thankfully, the data has been sourced from two different commercial information providers so it can't be entered in a uniform way as both have different systems.