How can I count the number of common words between two different cells/strings?

[nevarine]

Hey Excel ninjas,

I need to calculate the number of common words between two cells.

For example:
A1: The quick brown fox jumps over the lazy dog
A2: The dog jumps under the fox

In this case, the formula would return 5, as there are5 common words.

If possible, I'd like to not use any helper cells/VBA.

Thanks!

 

[bosco_yip]

Try,

In B2, enter CSE formula (confirmed enter with Ctrl+Shift+Enter) :

=COUNTA(FILTERXML("<a><b>"&SUBSTITUTE(A2&" "&A3," ","</b><b>")&"</b></a>","//b[preceding::* =.][not(following::* =.)]"))

 

[nevarine]

Bosco, excuse my French, but you're a f*cking genius. Thanks so much!