Forum Discussion

LWMorton's avatar
LWMorton
Copper Contributor
May 20, 2019
Solved

Searching For Text

I am looking to create a formula that searches a cell full of text and if it contains words from a certain list, the cell will display "0", if it contains words from a different list, then it will di...
excel
Formulas and Functions
office 365
training
User Adoption
  • SergeiBaklan's avatar
    SergeiBaklan
    May 20, 2019

    LWMorton , perhaps

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH($AF$2:$AF$8,B2))),
      4,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AE$2:$AE$8,B2))),
      3,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AD$2:$AD$8,B2))),
      2,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AC$2:$AC$8,B2))),
      1))))

    in C2 and copy it to another cells

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 20, 2019

LWMorton , perhaps

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($AF$2:$AF$8,B2))),
      4,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AE$2:$AE$8,B2))),
      3,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AD$2:$AD$8,B2))),
      2,
      IF(SUMPRODUCT(--ISNUMBER(SEARCH($AC$2:$AC$8,B2))),
      1))))

in C2 and copy it to another cells

LWMorton's avatar
LWMorton
Copper Contributor
May 20, 2019

SergeiBaklan that has worked perfectly, you are my saviour!

    • LWMorton's avatar
      LWMorton
      Copper Contributor
      May 21, 2019

      SergeiBaklan for some reason only half the equation works, the terms for level 0 and 1 are coming up as 2s. Any ideas to fix?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 21, 2019

        LWMorton ,

         

        Sorry, I forgot about 0 column. Plus we have to exclude blank cells, finally as

        =IF(SUMPRODUCT((LEN($AF$2:$AF$8)>0)*ISNUMBER(SEARCH($AF$2:$AF$8,B2))),
      4,
      IF(SUMPRODUCT((LEN($AE$2:$AE$8)>0)*ISNUMBER(SEARCH($AE$2:$AE$8,B2))),
      3,
      IF(SUMPRODUCT((LEN($AD$2:$AD$8)>0)*ISNUMBER(SEARCH($AD$2:$AD$8,B2))),
      2,
      IF(SUMPRODUCT((LEN($AC$2:$AC$8)>0)*ISNUMBER(SEARCH($AC$2:$AC$8,B2))),
      1,
      IF(SUMPRODUCT((LEN($AB$2:$AB$8)>0)*ISNUMBER(SEARCH($AB$2:$AB$8,B2))),
      0
)))))

Resources