Formula for manipulating text (delimited)?

[tangcla]

So, I have a number of text strings which look something like this, but many more of them:

13wk Full Outcome Brokered Stream 2 0-12
13wk Pathways Outcome Brokered Stream 3 13-60
13wk Full Outcome Assisted Stream 4 60+


I want to create a new column which will identify if the cells contain the word "Brokered", and the difficulty is that the character reference is not consistent across various fields.

Is there a formula which can basically look at a text string and get me the information I need?

edit: I ended up using this, which is a bit crude...

=IF(TRIM(LEFT(RIGHT(B3,23),9))="e Brokere","Brokered",IF(TRIM(LEFT(RIGHT(B3,23),9))="e Assiste","Assisted",TRIM(LEFT(RIGHT(B3,23),9))))

[DonkeyOte]

would something along the lines of:

=LOOKUP(2,1/SEARCH({"@"," Brokered "," Assisted "},B3&"@"),{"","Brokered","Assisted"})

work for you ?
(above assumes @ never appears within the strings)

[tangcla]

I'm not sure. I'll have to try it tomorrow