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 :)
