Search for multiple texts in cell, return specific text

[excelledsoftware]

Without VBA? my goodness I find that so much more difficult. In any case here is a formula that you will need to taylor for each array of strings you want to search.

=SUMPRODUCT(NOT(ISERR(SEARCH({"onald","ing","oil"},A2)))*{1,2,4})

Right after the search part add however many strings you need but then you need to update the number array as well by taking the previous number * 2.

For example to check for all weekdays in a string the formula would have to look like this.

=SUMPRODUCT(NOT(ISERR(SEARCH({"Mon","Tue","Wed","Thu","Fri"},A2)))*{1,2,4,8,16})

I use office 2003 at my house and 2013 at work so I think the ISERR is the correct formula normally I type IsError but I think that changed in newer version of office.

If there is a match found a number will be returned. You can enclose this in a iferror formula to get it to read "" if there is no match.

If this were me I would go insane having a bunch of formulas like this when I could just have a script that runs when I click a button and checks the strings in a matter of seconds. No waiting for formulas to calculate and I can add as many search terms as I want.

Let me know if you want to do the VBA route and I will be happy to write something up for you.

Thanks