VBA to search cell containing specific text and return common value

Hi I am a amateur into VBA Excel. Not doubt wasnt easy for me but i tried to use this forumlae for work.

Thus, would like to seek the help on the following work example which can be replaced by a VBA.
My intention is to expand the category to go beyond more than 7 types with the help of a VBA to formulae by cell:

Column "A" - Highlights of work issue
Column "B" - Category with formulae of "=IF(ISNUMBER(SEARCH("bubble",A2)),"Bubble on Tape",IF(ISNUMBER(SEARCH("particle",A2)),"Particle on Tape",IF(ISNUMBER(SEARCH("stain",A2)),"Stain on Tape",IF(ISNUMBER(SEARCH("u-shape",A2)),"U-shape bubble on Tape",IF(ISNUMBER(SEARCH("offset",A2)),"Tape offset",IF(ISNUMBER(SEARCH("winkled",A2)),"Tape Winkled","Others"))))))"

Attached is the excel.Work Example01.xlsx

Any form of help and assistance is greatly appreciated!

Thanks & Regards,
Hate0lif3

See the use of VLOOKUP function
In you example
C2=VLOOKUP(A2,$E$2:$F$16,2,0)
where $E$2:$F$16 is the table

Hi PCI & Monimonika,

Thanks for the help!
The solutions provided are definitely different from what I could have use for this work example.

By any chance, if my intention is to modify the example and automated it with a VBA script. How would I be able to do so?
Given that I would like to search certain text within a cell of column "A" and return the key word to match the category names. And, output the category names into the cells of column "B".
In all, the data in both columns "A" and "B" will always increased over time.

Once again, any small or big help is greatly appreciated!

Regards,
Hate0lif3

Ops deleted due to dble reply.

Originally Posted by PCI

Please Login or Register  to view this content.

Hi PCI,

Just tried out your code and found an unusual glitch that may or may not be significant depending on how hate0lif3 will be using it. Or maybe it's a feature?

Namely, the script stops working for subsequent rows if the row in Column A at the bottom of the list is not filled in first.

Example: If A20 is the last highlight item, inputting text into A22 does not fill in B22.

If the skipped A21 is then inputted with text, the script fills in for B21.

However, re-inputting text for A22 and onward still does not fill in for B22 and onward.

To get the script working again, the text in A22 (as well as all subsequent cells) has to be deleted completely (Enter key pressed) and then new text inputted.

This glitch only occurs in the above very unusual usage, so most likely it is not worth worrying about. But I wanted to point it out just in case.

Thanks PCI and Monimonika!

:P I prefer PCI method as I need to integrate as another sub function for my work vba!