Extract the word which carries the specific text

[pratheesh1983]

Hi everyone, In order to extract a word (s) that has a specified text in the word string, I'm searching for an Excel formula. I've included a couple examples of how the data should look below.

The table below, consisting of three columns, displays the text for searching in the first column, followed by a word string in the second column for searching words that match the text in the first column. Column 3 should contain the extracted data using the formula.

I have provided a few samples and expected results, which the Excel formula should extract in column 3. I would really appreciate it if anyone could help me in getting the formula that I can use in column 3.

1	2	3
Text To be Searched	Text String which needs to searched	Extracted Word
ESCO	FRANSISCO MARTIN RIESCO	FRANSISCO
ESCO	GIVESCO BAKERY A/S	GIVESCO
ES CO	SHIPPING LINES CO. LTD	LINES CO.
ESCO	CUOTA SPCIS (NINA PALMERO ESCOFET)	ESCOFET
ESC O	LOYER ESC OCT 24	ESC OCT
ESC O	ALIMENTARE FRANCESC O LUPOLI	FRANCESC O
COES	BATISTA CONTRUCOES SA	CONTRUCOES

 

[Cubist]

Can we assume that the substring will always exists in the text?

 

[Joe4]

If you first example is looking for "ESCO", shouldn't it return "RIESCO" and not "FRANCISCO"?

 

[pratheesh1983]

If you first example is looking for "ESCO", shouldn't it return "RIESCO" and not "FRANCISCO"?

Yes, you are correct. It's should be RIESCO

 

[JEC]

With the new regex functions if you already have them

=REGEXEXTRACT(B1:B7,"\pL*"&A1:A7&"\pL*")

 

[Cubist]

First pass.

Book1
	A	B	C	D	E
1	Text To be Searched	Text String which needs to searched	Extracted Word	Formula	Check
2	ESCO	FRANSISCO RIESCO MARTIN	RIESCO	RIESCO	TRUE
3	ESCO	GIVESCO BAKERY A/S	GIVESCO	GIVESCO	TRUE
4	ES CO	SHIPPING LINES CO. LTD	LINES CO.	LINES CO.	TRUE
5	ESCO	CUOTA SPCIS (NINA PALMERO ESCOFET)	ESCOFET	ESCOFET)	FALSE
6	ESC O	LOYER ESC OCT 24	ESC OCT	ESC OCT	TRUE
7	ESC O	ALIMENTARE FRANCESC O LUPOLI	FRANCESC O	FRANCESC O	TRUE
8	COES	BATISTA CONTRUCOES SA	CONTRUCOES	CONTRUCOES	TRUE
9	R ESC O	OCT LOYER ESC OCT 24	LOYER ESC OCT	LOYER ESC OCT	TRUE
Sheet2

Cell Formulas
Range	Formula
D2:D9	D2	=LET(s,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1,t,TEXTSPLIT(B2,," "),TEXTBEFORE(TEXTJOIN(" ",1,IF(SCAN(0,LEN(t),SUM)+1>=SEARCH(A2,B2),t,""))&" "," ",s))
E2:E9	E2	=C2=D2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E9	Cell Value	=TRUE	text	NO
E2:E9	Cell Value	=FALSE	text	NO