0

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

PedroPinto
  • 21
  • 1
  • 1
  • 7
  • see https://stackoverflow.com/questions/12546189/in-excel-how-can-i-create-the-intersection-of-two-strings-without-dropping-i – Custards1 Jan 26 '23 at 23:23
  • [Here](https://stackoverflow.com/a/62726978/9758194) a more recent post which in itself again is a bit outdated. Can you let us know what version of Excel you are using? – JvdV Jan 26 '23 at 23:31
  • Custards1 i tested stringintersect but does,t work. @JvdV office 2021 professional – PedroPinto Jan 27 '23 at 00:18
  • Not being able to use `TEXTSPLIT`, makes it hard or a large formula, because you cannot define a `LAMBDA` function inside `LET`, so everything needs to be repeated for cell `A1` and `B1`. If you want to go that road, you can check my [answer](https://stackoverflow.com/questions/25316094/split-a-string-cell-in-excel-without-vba-e-g-for-array-formula/74092401#74092401) to a similar question. Once you have the information split, then you can use `XLOOKUP` or `INDEX/XMATCH` for example to find the match. Better to go with `FILTERXML` not available for Mac as suggested @JvdV – David Leal Jan 27 '23 at 01:00

3 Answers3

1

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))
Shane S
  • 1,747
  • 14
  • 31
1

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(),' +'))]]"))
JvdV
  • 70,606
  • 8
  • 39
  • 70
0

I have tried & I got the below ans..

Result1

in Cell D3

=FILTER(TEXTSPLIT(TEXTJOIN(" + ",,A3:B3)," + "),TEXTSPLIT(TEXTJOIN(" + ",,A3:B3)," + ")=C3)

with the Filter function the result will be array.

and the basic result which supports all versions of excel is

Result2

in Cell D3

=IFERROR(MID($A3&" + "&$B3,SEARCH("#",SUBSTITUTE($A3&" + "&$B3,$C$3,"#",COLUMNS($D3:D3))),LEN($C$3)),"-")

have to drag the formula right side until get the result "-".

Manoj
  • 411
  • 1
  • 8