Excel Search Text String from List within Cell and output found text

%3CLINGO-SUB%20id%3D%22lingo-sub-1216544%22%20slang%3D%22en-US%22%3EExcel%20Search%20Text%20String%20from%20List%20within%20Cell%20and%20output%20found%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1216544%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20product%20descriptions%20(Column%20A)%20that%20contain%20the%20color%20and%20product%20type.%20I'm%20trying%20to%20separate%20the%20color%20into%20another%20column%20(Column%20C)%20so%20that%20I%20can%20sort%20by%20Design%20%23%20(Column%20B)%20and%20then%20Color%20(Column%20C).%20I%20have%20a%20separate%20list%20of%20possible%20colors%20I%20would%20like%20to%20use%20to%20search%20with%20(Column%20E).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rg1008_1-1583681081820.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F175833i0BC638B340A2DF57%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22rg1008_1-1583681081820.png%22%20alt%3D%22rg1008_1-1583681081820.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20on%20what%20to%20use%20for%20a%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20suggestions%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1216544%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1216561%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Search%20Text%20String%20from%20List%20within%20Cell%20and%20output%20found%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1216561%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F576985%22%20target%3D%22_blank%22%3E%40rg1008%3C%2FA%3E%26nbsp%3BAttached%20something%20based%20on%20what%20I%20recently%20created%20in%20another%20topic%20in%20this%20forum.%20I%20believe%20it%20will%20work%20for%20you.%20Just%20make%20sure%20you%20enter%20your%20colours%20in%20the%20%22keyword%22-list%20(Named%20Range)%20in%20the%20correct%20order.%20Enter%20all%20the%20variations%20first%2C%20then%20just%20the%20%22single%20colour%22.%20Have%20a%20look%20at%20the%20file%20and%20you'll%20understand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20need%20more%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2321351%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Search%20Text%20String%20from%20List%20within%20Cell%20and%20output%20found%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2321351%22%20slang%3D%22en-US%22%3ERiny%2C%3CBR%20%2F%3EThank%20you%2C%20this%20is%20an%20awesome%20solution.%20Helped%20me%20get%20exactly%20what%20we%20needed.%3CBR%20%2F%3EFor%20those%20who%20may%20not%20know%2C%20to%20edit%20the%20%22Keywords%22%20click%20on%20Formulas%2C%20Name%20Manager.%20There%20you%20will%20see%20the%20%22Keyword%22%20list%20defined.%3CBR%20%2F%3EBest%20Regards%2C%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

I have a list of product descriptions (Column A) that contain the color and product type. I'm trying to separate the color into another column (Column C) so that I can sort by Design # (Column B) and then Color (Column C). I have a separate list of possible colors I would like to use to search with (Column E).

 

rg1008_1-1583681081820.png

 

Any ideas on what to use for a formula?

 

Thank you for any suggestions

2 Replies

@rg1008 Attached something based on what I recently created in another topic in this forum. I believe it will work for you. Just make sure you enter your colours in the "keyword"-list (Named Range) in the correct order. Enter all the variations first, then just the "single colour". Have a look at the file and you'll understand.

 

Let me know if you need more help.

Riny,
Thank you, this is an awesome solution. Helped me get exactly what we needed.
For those who may not know, to edit the "Keywords" click on Formulas, Name Manager. There you will see the "Keyword" list defined.
Best Regards,