Forum Discussion

Rene81's avatar
Rene81
Copper Contributor
May 31, 2023

Cells contain specific text return value

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?

Formula:

=IF(ISNUMBER(SEARCH("T-Shirts",G17)),"Shirts")

 

Tried but not working:

=IF(ISNUMBER(SEARCH("T-Shirts",G4)),"Shorts"),IF(ISNUMBER(SEARCH("T-Shirts",G4)),"Shorts")

 

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 

 

Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 31, 2023

    Rene81 

    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:

     

    =IFERROR(INDEX(Types, MATCH(TRUE, ISNUMBER(SEARCH(Types, G2)), 0)), "")

Resources