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

• Objective
  • Map supplier SKUs (product IDs) to our internal SKUs (e.g. ABC00240404XP R/T US28 --> ABC-4040XP-689)
• Data
  • 100,000+ SKUs
  • Multiple brands, product lines, and models
  • Varying SKU nomenclature between suppliers (e.g. 1 product, 2 different supplier SKUs)
• SKU overview
  • Nomenclature can vary (e.g. use of dashes, spaces, and different finish/color IDs)
• Goal
  • I need to extract one or more values/IDs/criteria from within the SKU string
• The criteria will vary depending on the brand or product line
• SKUs also vary in structure/format and length (len() is <10 to 50+)
  • Use of dashes (-), spaces, and other characters to separate the different code

Objective/desired outcome

1. Extract the needed values/text
2. Transform the data (e.g. convert finish code: e.g. from: US28 --> to: 689)
3. Create a new SKU based on our internal nomenclature

 

[Jeffrey Mahoney]

Please be more specific. It sounds like each of your SKU suppliers have added unwanted characters and formats. And it sounds like each one would require different rules to make them compatible.

I can see in this string: ABC00240404XP R/T US28 that you removed 002, R/T and replaced US28 with 689. Is that the same for all your suppliers?

 

[zen]

Please be more specific. It sounds like each of your SKU suppliers have added unwanted characters and formats. And it sounds like each one would require different rules to make them compatible.

I can see in this string: ABC00240404XP R/T US28 that you removed 002, R/T and replaced US28 with 689. Is that the same for all your suppliers?

I accidentally hit submit prematurely.

Suppliers have not added "unwanted" data per se, it's just not useful for this exervise.

We only need components of the SKU.

Your observation is correct – we've 1) extracted, and 2) normalized, and 3) concatenated in our desired format.

Image attached.