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:
It will spill multiple results (see row 6), you can TEXTJOIN if needed.