How many words match between two sentences? I need an excel formula for that.

[Chris Manning]

I have two sentences. I need a formula determining how many words match between the two sentences?

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Can the sentences contain any punctuation?
2. If a solution by macro is possible is that acceptable?
3. Could we have 5 or 6 sample pairs of sentences (any sensitive data disguised or removed) with the expected results so that we can see the sort of variety that you might have? Preferably with XL2BB

 

[Chris Manning]

To answer…
1. Some of the sentences might have punctuation
2. I prefer to just use a formula not a macro
3. For example A1 has the sentence “today I ate green fish” and b1 has the sentence “ate fish”. I want a formula that returns “2” (since Both the words ”are” and the word “fish” match between the two sentences

 

[Peter_SSs]

2. I prefer to just use a formula not a macro

I doubt that you will be able to achieve that, particularly given the answer to point 1.

3. For example A1 has the sentence “today I ate green fish” and b1 has the sentence “ate fish”.

That hardly addresses my request for 5 or 6 samples showing variety. It also seems to ignore the answer to point 1.

Example:
"I ate Tom's fish" and "Tom ate fish"
Do you count that as 2 match or 3?

So, to help anybody who might try to suggest a formula approach (or macro), could you re-address point 3?