Extract a Sentence, That Contain One of these Words, from Text

Hi,

You don't say what the result should be if more than one of the search strings is found. This will return the sentence containing the first found of your search strings (as listed from left to right):

=IFERROR(TRIM(MID(SUBSTITUTE("."&A3,".",REPT(" ",999)),LOOKUP(2^15,SEARCH({"pictures","memory","video","features","capacity","price","quality"},SUBSTITUTE("."&A3,".",REPT(" ",999))))-499,999))&".","")

Regards

You're welcome!

In a nutshell:

1) Replace all occurrences of "." in the string with a large number of blank spaces (999 to be precise)

2) Find the position of the first occurrence of one of the search strings in this newly-formed string

3) Subtract 499 from this value. This is almost certain to be before the start of the sentence containing that word (unless you have a very long sentence!), though not encroaching on the previous sentence. Somewhere in the large blank void in between...

4) Apply a string length of 999. This will go from the blank void through the required sentence and into the second blank void.

5) Trim the result

Note that the formula may fail if you have occurrences of "." in the string which are not used to mark the end of sentences, e.g. in numbers/dates. And, as I mentioned, if you have some extremely long sentences (don't apply it to Saramago's works).

Regards