Problem description & analysis

Below is Excel data:

We are trying to get the common words of the two comma-separated word strings. Below is the expected result:

hello,you

Solution

Use SPL XLL plug-in

Write the formular in a blank cell:

=spl("=(?1.split@c()^?2.split@c()).concat@c()",A1,A2)

As shown:

Return:

Explanation: split A1,A2 into a sequence by comma，calculate the intersection, then concat into string

Q & A Collection

https://stackoverflow.com/questions/62952242/compare-each-word-in-a-list-of-word-in-excel-with-list-of-words-java-vba-exc