I have a table with a "Notes" column where multiple notes from users appear inside a single cell. Each notes starts with DATE and ends with ENDNOTE.
Example of a note within a cell:
DATE 2017-08-23 15:00:55 UTC NOTE:
forgot to go over the Consent letter with the customer since he has no email address.
Please read out the consent to the client or ask for an email address that we can forward it to.
Customer is little bit upset with his situation and may be rude. ENDNOTE\\
My goal is a formula, or series of formulas, that looks for users who have had unsatisfactory experiences by scanning for keywords, such as "upset" or "frustrated".
I have the following formula that returns the first instance of "upset" plus the rest of the string until ENDNOTE plus 20 chars preceding it.
My goal is a formula that extracts the entire string between DATE and ENDNOTE if "upset" and/or "frustrated" etc. appears between them.
I actually have about a dozen keywords and phrases to search for so I would be very open to a solution that used a =index(match( sort of approach using a helper table containing the keywords.
Thanks in advance!