The following would give you the word-index of the first word that contains your substring:
=SEARCH(B1,A1)-LEN(SUBSTITUTE(LEFT(A1,SEARCH(B1,A1))," ",""))+1
If you have access to FILTERXML you can retrieve it by throwing in some MATCH and this would return the word-index of an exact match:
=MATCH(B1,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"),0)
Note: The second formula needs to be entered through CtrlShiftEnter
As per @BigBen, you might run into troubled water if your string holds special characters like comma and the likes. In that case, you might want to throw in more nested SUBSTITUTE formulas to take care of that scenario.
If you need multiple matches, and you want it concatenated into a single cell, you'll need access to the TEXTJOIN function. To get all word-indexes of words that contain your substring:
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(B1,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"))),ROW(A1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),""))
To get all exact matches that hold your lookup value:
=TEXTJOIN(", ",TRUE,IF(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s")=B1,ROW(A1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),""))
Note: Both formulas needs to be entered through CtrlShiftEnter
Yeah. This problem is non-trivial.
@BigBen, not quite =)
Well what if the sentence was
"The dog ran, then sped, behind the house and then jumped the fence."?HA touché! @BigBen, that would require a few more
SUBSTITUTESto be thrown in =)^^^ Exactly ^^^. Which is why a simple example blows up when OP tries to apply it to the actual data at hand. You can imagine the follow-up questions :-)