Cells contain specific text return value

Copper Contributor

Hi all

I need help please

The below works well in finding the word T-Shirts in a sentence in a column BUT I want to look for more than just that, I need to include anything that contains Shorts in the other rows and return the word Shorts, how do I add that in?




Tried but not working:



My data in column G looks like this:

000000-Mens Apparel Branded Shorts
000000-Mens Apparel Branded T-Shirts
000000x-Ladies Apparel Branded Denim

I just want to return if it contains Shorts, than Shorts, if it contains Denim, then Denim

Hope that I am making sense

Thank you 


2 Replies


Enter all the words you want to look for in a range, one per cell.


Select the range.

Click in the name box on the left hand side of the formula bar.

Type a name, for example Types, and press Enter.


Let's say your data begin in G2.

Enter the following formula in H2, then fill down:



@Hans Vogelaar Thank you so much that works that has truly been a great help:)