Extract text from 2 strings on excel-CodePudding

I need help to do this.

I have on: A1 cell "Portugal Spain UK Belgium" B1 cell " France Germany Spain italy"

And i want on D1 the common word on a1 and b2. in this example "spain"

is this possible?

tks

CodePudding user response：

I would use "Text to Columns" like described here. Next I would use the formula =FILTER(list1,COUNTIF(list2,list1))

So consider that list1 is in cells A1:D1 and list2 is in cells A2:D2 the next cell (what ever cell) would have the formula:

=FILTER(A1:D1,COUNTIF(A2:D2,A1:D1))

CodePudding user response：

I've looked at documentation and think the following would be supported within Excel 2021:

=LET(x,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"),TEXTJOIN("   ",,FILTER(x,ISNUMBER(FIND("  "&x&"  ","  "&B1&"  ")),"")))

Or, if you would be interested in a xpath based solution:

=TEXTJOIN("   ",,FILTERXML("<x><t>"&TEXTJOIN("</s></t><t>",,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s")&"<s>  "&B1)&"  </s></t></x>","//t[.//*[contains(.,concat('  ',../text(),'  '))]]"))