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

[Santhosh_V]

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" is founds in the cell, output should be "shared mailbox" or 

If "Distribution" is founds in the cell, output should be "DL" or 

If "Non-standard" is founds in the cell, output should be "Corporate request" or

If "NSSR" is founds in the cell, output should be "Non-Standard Service request"

Looking for to get help on this!!

Thank you. 

[#2 · Accepted solution]

@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.

[#3]

Thank you so much @HansVogelaar

[#4]

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

[#5]

@Razvan1610 

Like this:

=IF(ISNUMBER(SEARCH("[INTERN]",A2)),IF(ISNUMBER(SEARCH("Offensive",A2)),"INTERN Offensive","INTERN"),IF(ISNUMBER(SEARCH("Offensive",A2)),"Offensive","Regular"))

[#6]

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.

[#7]

@Razvan1610 

You're welcome (greetings from The Netherlands)

[#8]

@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!

[#9]

@sroy2000 

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.

[#10]

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.

[#11]

Hi @Hans Vogelaar 

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!

[#12]

@SBays123 

How does cell A8 list the symbols? Separated by commas, or by spaces, or by line breaks, or ...?

[#13]

@HansVogelaar 

By dashes "-"

I believe an ISNUMBER(SEARCH)) would do it, not sure how though... 

[#14]

@SBays123 

Thanks. It's a bit more complicated, since "TR" is part of "TRA".

Which version of Excel do you use?

[#15]

@HansVogelaar 

I am not sure exactly, but it is one of the latest versions. 

Here is what I have now, this formula works for everything EXCEPT the TR and TRA issue. It will some TR and TRA if you type in TRA, just like you said. 

[#16]

@SBays123 

Use this:

=SUM(ISNUMBER(SEARCH("-"&$F$7:$F$31&"-","-"&$B$4&"-"))*$G$7:$G$31)

[#17]

@HansVogelaar 

That works, thank you much! You are super helpful man!

[#18]

@HansVogelaar 

I have one other excel template I am struggling with, it is a linear interpolation calculator that will formulate a table based off of interpolated values.

I attached a pic below, essentially, I want to take the data from the table on the left, and I want to find the Flow at every 1 PSI between the two interpolated values in the table. Example, for the first set it is between 70.41 & 66.66, so I want to find flow at 70, 69, 68 & 67. Then 66.66 is already there and I would continue on down to 66, 65 and so on...

I feel like I'll use the round down function to create an Array, but I am not sure. Any help is appreciated!!

[#19]

@SBays123 

I don't see the screenshot.

Could you attach it again, or a sample workbook?

[#20]

@HansVogelaar