There are many ways to do each piece of the problem, but the combination of requirements points to one approach that is simple and straightforward, or a VBA solution. The non-VBA solution uses helper columns. The layout of your spreadsheet is unknown, as are other factors, like the number of sentences and the possible number of results, and how you need and want to use the results. So this answer will be more generic guidance than a specific solution.
Using functions, it will be complicated to search the sentence for potentially more than one target. A simpler approach is to break the sentence into words and then check the words. A simple way to parse the sentences is with the built-in Convert Text To Columns wizard.
Assuming the sentences are in column A, leave the next few columns blank for your results. That way, your results will always be in a known location, visible next to your data. I'll assume a maximum of two, so columns B and C will contain result values.
Figure the maximum number of words there can be in a sentence, and reserve that many subsequent columns for helper columns. These will be used to test each word. Lets say you allow for five words. Columns D:H will contain formulas pointing to columns I:M, respectively.
Lets say row 2 is your first data row, so the first formula will be in D2, pointing to I2. The formula will be similar to what Gary's Student suggested:
An alternate formula would be:
That just gives you a couple of ideas about how to tell whether the target word fits the pattern. The second formula uses SEARCH (because it isn't case-sensitive), to see if the last character is in your list. Testing the first two characters for
IN, and the last character, each result in True or False, which Excel treats as
0, respectively. So if both tests are true the result is
In the SEARCH expression, I started with a blank and test for a result >1. The reason I did this is because I used MS Office Online to test it, and it thinks the rightmost character of an empty cell is found in position 1 of the test string.
If SEARCH does not find a match, it returns an error, so wrapping the formula in IFERROR ensures that any word not matching results in
So either formula returns
1 if the word fits your pattern, or
0 if it doesn't (or if its target cell is empty).
Enter either formula in D2, and drag or copy it across to H2, and down to the last row you need.
To fill the target cells, select all the data in column A, and copy and paste it into column I (Text to Columns overwrites the data and starts in the data column). Select the data in column I. On the
Data tab, in the
Data Tools group, click
Text to Columns. You'll get a wizard that is self-explanatory.
Tell it the data is delimited, and select
space as the delimiter. It will give you a preview of how it thinks you want it to parse the words. When you finish the wizard it will put each word in a consecutive cell in the row.
Your formulas in D:H should show at least one
1 in each row. Those are your words, and their column is in the same relative position as the parsed word.
To get the first word's value in column B, use INDEX and MATCH to find the first
1 in the row in D:H and retrieve the corresponding value from the same row in I:M. For example, B2 would be:
This retrieves the value from the cell in I2:M2 corresponding to where an exact match to
1 was found in D2:H2.
To get the subsequent values for column C (and beyond if there are more than two), there are a number of solutions to find the Nth matching
1. Here is a link to one method.