• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to assign category by keyword in Excel

chuff76

New Member
Hello Excel Guru's !

I have an excel spreadsheet which contains a list of free text descriptions.

What I would like to achieve, is for excel to read these free text fields and if it finds certain predefined keywords, that it should assign them to a certain category.

I thought my excel knowledge was okay, but I'm not really sure where to start with this - The logic behind it seems fairly straight forward but I am not even sure if this is achievable using excel so I thought I'd ask the experts!

I have attached a stripped down sample, which just contains the data for this problem.

Very briefly;
if it reads the Sheet1/Job Title column and finds any of the keywords from Data/category keywords, then it should assign the appropriate category

I hope that my description makes sense! :confused:

Many thanks for reading
 

Attachments

  • Jobs by keyword.xlsx
    11.9 KB · Views: 26
Hi Nebu

That looks like it works perfectly, thank you so much!

I will transfer the formula to the main spreadsheet and see if I can make it work over there.

I am very grateful for your help, thank you thank you!!

Chuff76
 
Hi Khalid NGO

Excellent! your solution also achieves the desired result!
My only concern with this method, is that my list of keywords may grow over time.
Logically it seems to make more sense to me though, as I do not understand arrays and I am looking at my screen a bit confused as I try to implement Nebu's beautiful solution.
I will attempt both methods and see which I can make work

Thank you for your fantastic reply Khalid
 
Hi Khalid NGO

Excellent! your solution also achieves the desired result!
My only concern with this method, is that my list of keywords may grow over time.
Logically it seems to make more sense to me though, as I do not understand arrays and I am looking at my screen a bit confused as I try to implement Nebu's beautiful solution.
I will attempt both methods and see which I can make work

Thank you for your fantastic reply Khalid

Hi,
Thanks for the words,
But Personally I think Nebu's answer will be dynamic and will cover more keywords.

In my answer
Main part is just:
=IF(ISNUMBER(SEARCH("ASP",$B2)),Data!B$2
Where it searches for the keyword and return the match, if the keyword not found in first IF statement, then move to the next IF; and so on....
You will need to implement the same logic if your keyword grows time to time.

Regards,

Khalid

P.S: Hope someone will give you another non-array alternate.
 
Last edited:
Hi:

If the keywords are going to grow ,If conditions are not your best friend. I believe there is a limit to the no of If conditions you can write in a single formula.

Thanks
 
Hi Guys,

I found this very useful.

Is there a way of using defining a category if it ONLY includes the EXACT keyword. Not just if it has been included somewhere in the cell?

Would be handy if it could do both.

Thanks,
Nick
 
Nested IF levels limit is 64 in Excel 2007 and later but the formula will also become difficult to maintain.

You can use a LOOKUP based construct like below:
=IFERROR(LOOKUP(2^15,SEARCH(Data!$A$2:$A$13,Sheet1!B2,1),Data!$B$2:$B$13),"Not found")

As to the query posted by Nick Bright:
Welcome to Chandoo.org forums. You can use a standard function like VLOOKUP or INDEX/MATCH construct to do it (just use 0 or FALSE in the last argument for exact match).
 
Hi.

Thanks for getting back to me.

Unfortunately that wont complete the job for me.

I am attempting to categorise both exact match and partial match within the same formula.

Is this possible?
 
Back
Top