CODEWITHSUNDEEP
excelexcel-formula
PedroPinto
PedroPinto

Reputation: 21

Extract text from 2 strings on excel

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

Upvotes: 0

Views: 110

Answers (3)

Manoj
Manoj

Reputation: 461

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 "-".

Upvotes: 0

JvdV
JvdV

Reputation: 75990

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(),' +'))]]"))

Upvotes: 1

Shane S
Shane S

Reputation: 2303

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))

Upvotes: 1

Related Questions