Two Versions of Nested IF functions using SEARCH functions in Excel

I have found these two formulas to be very useful.


 1) Use case is text mining for a certain theme in an open ended response. For example people who mention loving the outdoors we want to put in a category of people who mentioned the outdoors using various synonyms for that.

=IF(OR(ISNUMBER(SEARCH("outdoors"A2)),ISNUMBER(SEARCH("outside",A2)), ,ISNUMBER(SEARCH("nature",A2)),ISNUMBER(SEARCH("air",A2)),ISNUMBER(SEARCH("sunshine",A2))), "Nature lover", 0)

2) This version seeks to categorize each row based on one column of data. It searches for a word and if that word is found the output is specified, then it searches for a different word of interest and puts another specified value if that word is present.. The difference is that with the first formula the output is a binomial variable. This version has a potentially limitless number of categories as outputs.

=IF(ISNUMBER(SEARCH("tab", D2)), "Tablet", IF(ISNUMBER(SEARCH("dsk", D2)), "Desktop","Mobile"))


Hope this is helpful :)

Excel File With US States, Abbreviations, Regions, Sub-Regions

Great for formatting data using Vlookups: I just ran into this issue and couldn't find this on the web anywhere so I am sharing it here. LMK if you have issues.

Click Link Below to Download
US States, State Abbreviations, Regions, Sub Regions Excel File