Extract 1-N value(s) from a string of text in 1 cell - multiple extraction values in separate tables (excel formula needed)

[zen]

Thank you in advance for the help! ?

After extensive forum research, I decided to post since I was unable to find a comparable problem/solution.

Summary
I need a formula that can extract a value from within a string of text based on a list of multiple criteria to look for (e.g. if value 1, 2, 3, N exists, extract it).
Each extracted value will need to appear in a cell in a column that is used to store a category of attributes.

• Objective (SKU mapping)
  
  • Map supplier SKUs (product IDs) to our internal SKUs (e.g. ABC00240404XP R/T US28 --> ABC-4040XP-689)
  • Need to identify text within a supplier SKU, recognize that text, and map it to our corresponding SKU
  • Need a framework/formula that can be applied at scale with minimal modification (ideally where Excel beginners can use / apply it in the ongoing)
• Data
  
  • 100,000+ SKUs
  • Multiple brands, product lines, and models
  • Varying SKU nomenclature between suppliers (e.g. 1 product, 2 different supplier SKUs)
  • The location of the text value *may* influence what it represents for the product
• Bonus
  
  • Some extracted values will need to be normalized and converted to values we use internally (e.g. FROM: US28 --> TO: 689)

 

[Fluff]

Duplicate to: Extract 1-N value(s) from a string of text in 1 cell - multiple extraction values in separate tables (excel formula needed)

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.