Search for multiple characters in a single cell

[Knob12]

I have a formula that I will enter into a cell and drag down to about 6,000 rows. I need get the value in column C of each row, up to a colon, dash, the word "FOR" (upper or lowercase), whichever comes first. Then, I need to remove all of the following words: "Hump YM", "Trim YM", or "NX". Lastly, I use TRIM to remove any extra spaces.

For example, if column C contains:

[TABLE="width: 1100"]
<tbody>[TR]
[TD="width: 1100"]Trim YM Remove  Block Deny Command Failed -- Invalid Slot Number -- Operator: 2Request: NBUC,[/TD]
[/TR]
</tbody>[/TABLE]

The formula should return:

Remove Block Deny Command Failed

The formula I came up with isn't working:

=TRIM(SUBSTITUTE(LEFT($C3,SEARCH({":","-","FOR","for"},$C3)-1),{"Hump YM","Trim YM","NX"},""))

Excel doesn't seem to be recognizing the lists given in {curly braces}, only the first item in each list. Please help!

 

[Ron Coderre]

With your target text in A1
and these lists of search terms in F1:G3

:     HUMP YM
-     Trim YM
FOR   NX

This formula parses that text to only display the error reason:

B1: =TRIM(MID(LEFT(A1,MIN(INDEX(SEARCH($F$1:$F$3,A1&":-FOR"),0))-1),LEN(LOOKUP(2,
1/COUNTIF(A1,"*"&$G$1:$G$3&"*"),$G$1:$G$3))+1,100))

Is that something you can work with?

 

[Knob12]

Thank you Ron. That did the trick.