Excel Macro/function For Finding Words From Sentence

I was wondering if someone can help with a vb macro which can save significant time.

I have a list of more than 5000 products information in excel sheet. I will upload all these product information via bulk upload feature on my online store.

My problem is assigning categories (sub-categories) to each product. Right now i have to do it manually.

Understaning

A1 has this sentence: Gold & Rhodium Plated Cz Earrings Necklace Set

Category column should be: Jewelry|Jewelry>>Earrings|Jewelry>>Earrings>>Necklace|Jewelry>>Earrings>>Necklace>>Set

It would be of great help if anyone can help me with macro.

In my opinion there should be a predefined list of words somewhere (may be different sheet of same file) and macro will prepare category by finding word from the list.

For example, Jewelry: earrings,necklace,set,rings,bracelet

If script finds 'rings' in sentence then it will prepare category like this 'Jewelry|Jewelry>>rings'

is this not possible?

Here's something crude I made. It works, but I think array formulas would be more elegant here. I've implemented array formulas before, but I'm not fluent enough to do this in the same amount of time I did what's attached. It would probably take even less time in more capable array formula hands, but not mine at this time. This will get you by in case no one else replies.

mateeben - finding words in sentence 3-24-15.xlsx

@elprup: extremely thanks. However, i am not able to download the file (it is asking me to login even if i am already logged in), probably because i became member yesterday.

Could you please use any file hosting.

Thanks

This is confusing. i can't download file from this thread. But i can download file posted in this thread: http://www.excelforum.com/excel-prog...-3-things.html