Forum Discussion

Santhosh_V's avatar
Santhosh_V
Copper Contributor
Jun 27, 2021
Solved

Need to search multiple words in a cell and get the output based on the word found.

Hi,   I am having a data in column A like below, then I need search for multiple words like "Generic Mailbox", "Distribution", "Non-standard", "NSSR" in the each cell and then if "Generic mailbox"...
developer
excel
Formulas and Functions
Macros and VBA
Office Scripts
  • HansVogelaar's avatar
    HansVogelaar
    Jun 27, 2021

    Santhosh_V 

    If you have Excel 2019 or Excel in Microsoft 365, enter the following formula in B1:

     

    =TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR"},A1)),{"Shared Mailbox","DL","Corporate Request","Non-Standard Service Request"},""))

     

    This allows for more than one of the search terms in a cell.

     

    Otherwise:

     

    =IFERROR(INDEX({"Shared Mailbox","DL","Corporate Request","Non-Standard Service Request"},MATCH(TRUE,ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR"},A1)),0)),"")

     

    Fill down from B1.

shobhit61066032's avatar
shobhit61066032
Copper Contributor
Sep 18, 2023

Hi HansVogelaar 
If i would like to add exception keywords, OR Keywords, AND Keywords with above query. Can you suggest with refrence of below example.

Product name BrandCategory
Huggies Natural Care Refreshing Baby Wipes, Scented, 3 Flip-Top Packs (168 Wipes Total)Huggies Wipes
Baby Wipes, Huggies Natural Care Refreshing Baby Diaper Wipes, Hypoallergenic, Scented, 10 Flip-Top Packs (560 Wipes Total)Huggies Diapers
Baby Wipes, Huggies Natural Care Refreshing Baby Diaper Wipes, Hypoallergenic, Scented, 10 Flip-Top Packs (560 Wipes Total)Huggies Diapers
Baby Wipes, Huggies Natural Care Refreshing Baby Diaper Wipes, Hypoallergenic, Scented, 10 Flip-Top Packs (560 Wipes Total)Huggies Wipes
Haggie Natural Care Refreshing Baby Wipes, Scented, 3 Flip-Top Packs (168 Wipes Total)HaggieWipes



Search 'Huggies' from A column,
AND
Search 'wipes' from A column
AND
Search 'Natural', 'Naturel'
NOT
Seach' Doesnot contain 'Diaper', 'Diapers', 'Pant'

 

Then Out Put should 'Natural'

 

 

 

  • shobhit61066032's avatar
    shobhit61066032
    Copper Contributor
    Sep 18, 2023

    HansVogelaar 

    Basically I am looking for a TEXTJOIN formula in excel, which i can use to search different keywords in A column. condition is mention below.

    I am having data in A column like below 

    then I need search for multiple words like "Huggies", "Distribution", "Natural", "Naturel", "Diaper", "Diapers", "Pant" in the each cell and then 

    if "Huggies" is founds with wipes with Natural not Diaper  then output should be Natural Wipes  

     

    Looking for to get help on this!!

    Thank you. 

     

Resources