Return Text From a Cell if It Contains One of Many Texts

[ScardiB]

I have a list of campaigns that contain multiple texts in each cell. I want to pull out a word from the cell but the text is not always in the same position or always between the same characters.

In the attached sheet I have 6 campaigns and I need to pull out specific words. If A2:A7 contains "Vegetable" then in column B I want it to say Vegetable, if in column A in says "Fruit", in column B write "Fruit", and so on.

Should I create a list of what I want to extract and use that? Like Column C has a list of the text options I want to pull out (Vegetable, Fruit, Dairy, Grain, Cheese, Corn)

Hope that makes sense.

[sandy666]

like this ?
(with Power Query)

Campaign		Criteria		Campaign	Criteria
brand category Vegetable intent exact		Vegetable, Fruit, Dairy, Grain, Cheese, Corn		brand Vegetable category intent exact	Vegetable
brand Fruit intent exact				brand Fruit intent exact	Fruit
brand category Dairy intent exact				brand category Dairy intent exact	Dairy
brand option Grain affinity exact				brand option Grain affinity exact	Grain
brand category Cheese intent exact				brand category Cheese intent exact	Cheese
brand option 2 Corn custom exact				brand option 2 Corn custom exact	Corn

[ScardiB]

Yes, like that. Is there a formula that can help pull that out?

[sandy666]

not formula but Power Query
Data tab - Show Queries - dbl click on any table - Advanced Editor

[Glenn Kennedy]

One way:

=IFERROR(LOOKUP(1E+100,FIND($G$1:$G$6,A2),$G$1:$G$6),"")

[Glenn Kennedy]

Or, since you have O365, a SPILL array that does it all in a single cell:

=BYROW(A2:A7,LAMBDA(x,LOOKUP(1E+100,FIND(G1:G6,x),G1:G6)))