Using a single cell with comma separated tags to tag a column of products

Question

Cell E1 contents has product tags:

Sets,Cuff,Bra,Panty,Garterbelt,Costume,Blouse,Skirt,Pantyhose,Corset,Headwear,Lace,Crotchless,Thong,Negligee,French Maid,Nurse,Trunk,Jock,Brief,Boxer,Athletic,Mesh,Fishnet,Fencenet,Lowrise,Micro,G String,Thigh Highs,Suspender,Bow,Boyshort,Babydoll;Teddy,Lace,Tube Dress,Boob Tube,Chemise,Glitter,Leopard,Satin,Halter,Eyemask,Ruffle,Kimono,Schoolgirl,Bodysuit,Camisole,Cami,Bodystocking,Bustier,Gloves,Tap Shorts,Shiny,Split Crotch,Ribbon,Open Crotch,Stretch,Fingerless,Leather

Column D2:D has all the lingerie product titles like "Black Garterbelt and Suspender"

So the idea is to print in column E2:E comma separated tags that are in the product tile from column D

Answer 1

Here's a formula that does this: the entry for E2 is shown but it can be dragged down.

=join(",", filter(split(E$1,","), regexmatch(D2, "(?i)\b" & split(E$1,",") & "\b"))) 

Explanation:

• split(E$1,",") split the set of tags into an array & "(?i)\b" & split(E$1,",") & "\b" turns each tag into a regular expression which means: match this string as a whole, case-insensitive.
• regexmatch records True if the product name matched the tag, and False if no match.
• filter filters the array of tags according to the output of regexmatch
• join joins the filtered array into a comma-separated string.