How to categorize data in excel based on the contents?

[Allan Sabado]

We have a file with thousands of data in it and before we usually just split the file among us and then categorize it based on the titles. I would like to seek your help to find out if there is any calculation that would automatically categorize it based on the criteria given? I have attached a sample if it helps.

Thanks in advance guys!

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[Allan Sabado]

Thanks Nebu, this is really helpful. Would you be able to describe in detail how the formula works? Sorry, I am not that good with excel.

 

[Allan Sabado]

By the way, would it be the same formula if say, I have 3 or more categories to choose from?

 

[Nebu]

Hi:
You can have "n" no of categories added to the list that is the beauty of this formula. In column "m" add all the keywords you want to search and in column "n" add all the categories and change the range in the formula.

Explanation:

The key component of this formula is search it searches for the keywords in the text in your case column "a" and if it find a match it will assign the corresponding categories.

In a cell you can hold up to 32767 characters 2^15 will give you 32768 this will ensure that the formula search for the entire cell.Hope this answers your question.

Thanks