Search code examples
excel

Select word from sentence containing certain letters

I want to select a word (D) from a sentence (B) based on some word/text (A) I give. I get output (C) using the following code:

=IF(ISNUMBER(SEARCH(A1;B1));A1;"NA")
A (word/text) B (sentence) C (output) D (wanted output)
apple I have an applepie apple applepie
bear this life is unbearable bear unbearable
hat the mad hatter is wise hat hatter
ness I met the loch-ness monster ness loch-ness

How do I display the entire word (e.g. applepie) in stead of just the letters I am looking for (e.g. apple) ?

Solution

  • Something like this would work:

    =FILTER(TEXTSPLIT(B2," "),IFERROR(SEARCH(A2,TEXTSPLIT(B2," ")),0))

    Result:

    enter image description here

    It will spill multiple results (see row 6), you can TEXTJOIN if needed.