Identifying subtext within text with multiple criteria


Hi everyone

I have a database with names of chemicals in multiple rows. Approx 80000 But this data set is not clean. 

For eg I have Benzene,  2-Chlorobenzene pure,  2-Chlorobenzene (99%pure), aminobenzene, 2,4-Di Chlorobenzene,3-chlorobenzene and several others. From this list, I want to extract 2-Chlorobenzene both pure and 99% pure for further processing.  However how do I get excel to automate the process to tag these as Chlorobenzene.  I am unable to use vlookup or search options as the names aren't unique. Is there a code to tag them.

Since this is  a monthly activity it is painful to apply filters and manually check in such a database  




Selected Answer

Hi there!

You can try using special wildcard characters like the multiplication sign within a vlookup.



This just requires a lookup table to be created that will output the correct corresponding tag. Replace Chlorobenzene with whatever you want or with a cell reference.


Answer the Question

You must create an account to use the forum. Create an Account or Login