Extracting a word from the text

[pratheesh1983]

I need an Excel formula that will find the word that has a certain text on it. In the table below, I've tried to show what I mean. In column A, you can see the word that needs to be found in column B of the text, and in column C, where the formula should go to get the needed word, I would appreciate it if anyone could help find a formula for it.

A	B	C
Word to be searched	Text	Extracted Word (Formula)
Susan	Susan Santos	Susan
Susan	Susanna Moy	Susanna
Susan	Susann Lowe	Susann
Susan	Elisabete Susana Sanchez	Susana
Susan	A.C.B Susanne	Susanne

Thanks in advance

 

[MountainFog]

Try pasting this in C2 and filling down:

=FILTER(TEXTSPLIT(B2," "),ISNUMBER(SEARCH(A2,TEXTSPLIT(B2," "))))

Note that in the case of:
"Susanne Susanna Susan" would result in Susanne in Column C, Susanna in Column D, and Susan in Column E

 

[KRice]

Another option in column D, using a similar idea shown by @MountainFog...and then if you want the results contained within a single cell separated with a delimiter (comma here), @MountainFog's formula could be wrapped with a TEXTJOIN:

MrExcel_20240112.xlsx
	A	B	C	D
2	Susan	Susan Santos	Susan	Susan
3	Susan	Susanna Moy	Susanna	Susanna
4	Susan	Susann Lowe	Susann	Susann
5	Susan	Elisabete Susana Sanchez	Susana	Susana
6	Susan	A.C.B Susanne	Susanne	Susanne
7	Susan	Susanne Susannadanna	Susanne, Susannadanna	Susanne, Susannadanna
Sheet5

Cell Formulas
Range	Formula
C2:C7	C2	=TEXTJOIN(", ",,FILTER(TEXTSPLIT(B2," "),ISNUMBER(SEARCH(A2,TEXTSPLIT(B2," ")))))
D2:D7	D2	=LET(a,BYCOL(TEXTSPLIT(B2," "),LAMBDA(c,IF(ISNUMBER(SEARCH(A2,c)),c,""))),TEXTJOIN(", ",,a))