Forum Discussion
Need to search multiple words in a cell and get the output based on the word found.
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.
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.
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 Brand Category 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) Haggie Wipes 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'
I'm afraid I don't understand what you want.
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.
HansVogelaar
Thanks for the great formula, it works perfectly for me.
I was wondering if there is a way to use a list of the texts, instead of typing it like the SEARCH({"xxx","yyyy"...})?Thanks in advance!
It depends. The above example is complicated because we have to check for the occurrence of two words together. If we only have to search for single words, we could use a lookup list.
Thanks HansVogelaar . I created a named range "list_FileExtension" and replaced the strings separated by comma in your formula, with the named range as below. It worked. Thank you for your help.
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(list_FileExtension,B16)),list_FileExtension,""))
The output came as required:5, 6, 7, 8, XNAP, pdf, Title
5, 6, 7, 8, XNAP, pdf, Title were part of the values, in the named range, to be searched in the column B.
Hi HansVogelaar
Just new around here and saw your formula that was almost great for what I need. Almost because it keeps giving me inaccurate results.
I just know some basic stuff in Excel and I'm not able to go through this.
So I have this data below where I need to insert in C2:C5 a formula that will return as follows:
If "[INTERN]" is found in the cell, output should be INTERN
If "Offensive" is found in the cell, output should be Offensive
If "[INTERN]" and "Offensive" is found in the cell, output should be INTERN Offensive
If none of the above, then the output should be "REGULAR"
I copied the formula and modified as needed, but something is not correct since for the last value it returns the same thing (C4 and C5 are the same). Would you be kind enough to help me solve this formula and have it return as I indicated in D2:D5? I'm struggling for some days now and no result. 🙂
Thanks so much for your help, looking forward to see where I did wrong. 😉
Razvan
I've read quite a few of your responses and they are all excellent, I have a request that is similar to these but adds one more element to it that I am unsure how to do...
I am needing to search cell A8 in my workbook for the following list of symbols. Cell A8 may contain as little as none of them or all of them. The symbols correspond to a price, I would like the output to sum up the price of each of the symbols found in cell A8. Hopefully you can help, thank you!
How does cell A8 list the symbols? Separated by commas, or by spaces, or by line breaks, or ...?
Like this:
=IF(ISNUMBER(SEARCH("[INTERN]",A2)),IF(ISNUMBER(SEARCH("Offensive",A2)),"INTERN Offensive","INTERN"),IF(ISNUMBER(SEARCH("Offensive",A2)),"Offensive","Regular"))
- Thank you very much for this HansVogelaar
It is indeed a great tool this Excel in the hands of someone who knows how to use it.
Many greetings from Romania.
- Thank you so much HansVogelaar
