Extracting SKUs with commas when comma is also the delimiter between SKUs?

JansiJansi

JansiJansi

New Member
Joined
Jun 22, 2022
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi all.

Product export puts all SKUs for the variation of that product (like color, size, etc.) in one cell. These SKUs have commas in them, and they are also separated by commas.

This is what it looks like:
eq7-5mm,gold,single,eq7-5mm,rose gold,pair,eq7-6mm,gold,single,eq7-6mm,rose gold,single,eq7-6mm,silver,pair,eq7-6mm,rose gold,pair,eq7-7mm,silver,single,eq7-7mm,gold,pair,eq7-7mm,rose gold,pair,eq7-8mm,gold,single,eq7-8mm,silver,single,eq7-10mm,silver,pair,eq7-10mm,rose gold,pair,eq7-12mm,gold,single,eq7-12mm,gold,pair,eq7-12mm,rose gold,pair,eq7-5mm+6mm,gold,single,eq7-5mm+6mm,rose gold,single,eq7-7mm+8mm,rose gold,single,eq7-8mm+10mm,silver,single,eq7-8mm+10mm,rose gold,single,eq7-5+6+7mm,silver,single,eq7-6+7+8mm,gold,single,eq7-7+8+10mm,gold,single,eq7-7+8+10mm,silver,single,eq7-7+8+10mm,rose gold,single,eq7-8+10+12mm,rose gold,single,eq7-5mm,silver,single,eq7-5mm,rose gold,single,eq7-5mm,gold,pair,eq7-5mm,silver,pair,eq7-6mm,silver,single,eq7-6mm,gold,pair,eq7-7mm,gold,single,eq7-7mm,rose gold,single,eq7-7mm,silver,pair,eq7-8mm,rose gold,single,eq7-8mm,gold,pair,eq7-8mm,silver,pair,eq7-8mm,rose gold,pair,eq7-10mm,gold,single,eq7-10mm,silver,single,eq7-10mm,rose gold,single,eq7-10mm,gold,pair,eq7-12mm,silver,single,eq7-12mm,rose gold,single,eq7-12mm,silver,pair,eq7-5mm+6mm,silver,single,eq7-6mm+7mm,gold,single,eq7-6mm+7mm,silver,single,eq7-6mm+7mm,rose gold,single,eq7-7mm+8mm,gold,single,eq7-7mm+8mm,silver,single,eq7-8mm+10mm,gold,single,eq7-10mm+12mm,gold,single,eq7-10mm+12mm,silver,single,eq7-10mm+12mm,rose gold,single,eq7-5+6+7mm,gold,single,eq7-5+6+7mm,rose gold,single,eq7-6+7+8mm,silver,single,eq7-6+7+8mm,rose gold,single,eq7-8+10+12mm,gold,single,eq7-8+10+12mm,silver,single

There are not always only 2 commas in a SKU. I need to get all the individual SKUs into their own cells, like this:

eq7-5mm,gold,single
eq7-5mm,rose gold,pair
eq7-6mm,gold,single
eq7-6mm,rose gold,single

Can anyone think of a way to do this easily? I have 815 rows to do this for and most have around the same amount of variations.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hi

=BYROW(WRAPROWS(TEXTSPLIT(A1,","),3),LAMBDA(X,TEXTJOIN(",",,X)))
 
Upvote 1
If you are looking for a code solution, usually, all you need is a reliable rule. Perhaps that is (to use verbal description of rule)
Find first "eq" (or "eq7") after the first one. Everything before that (minus the preceding comma) is what you want.
Start again from the point where you found the first eq and do it again.
Repeat until done.

Such an approach should also deal with variations that you might have, such as "eq7-5mm,gold,single/double"
I have no idea if the proposed formula would compensate for that, nor do I want to figure it out for myself :sick:
 
Upvote 1
Hi
Excel 365 Beta

=DROP(SUBSTITUTE("eq"&TEXTSPLIT(A1,,"eq")&" ",", ",""),1)
 
Upvote 1
Another option
Fluff.xlsm
AB
1
2eq7-5mm,gold,single,eq7-5mm,rose gold,pair,eq7-6mm,gold,single,eq7-6mm,rose gold,single,eq7-6mm,silver,pair,eq7-6mm,rose gold,pair,eq7-7mm,silver,single,eq7-7mm,gold,pair,eq7-7mm,rose gold,pair,eq7-8mm,gold,single,eq7-8mm,silver,single,eq7-10mm,silver,pair,eq7-10mm,rose gold,pair,eq7-12mm,gold,single,eq7-12mm,gold,pair,eq7-12mm,rose gold,pair,eq7-5mm+6mm,gold,single,eq7-5mm+6mm,rose gold,single,eq7-7mm+8mm,rose gold,single,eq7-8mm+10mm,silver,single,eq7-8mm+10mm,rose gold,single,eq7-5+6+7mm,silver,single,eq7-6+7+8mm,gold,single,eq7-7+8+10mm,gold,single,eq7-7+8+10mm,silver,single,eq7-7+8+10mm,rose gold,single,eq7-8+10+12mm,rose gold,single,eq7-5mm,silver,single,eq7-5mm,rose gold,single,eq7-5mm,gold,pair,eq7-5mm,silver,pair,eq7-6mm,silver,single,eq7-6mm,gold,pair,eq7-7mm,gold,single,eq7-7mm,rose gold,single,eq7-7mm,silver,pair,eq7-8mm,rose gold,single,eq7-8mm,gold,pair,eq7-8mm,silver,pair,eq7-8mm,rose gold,pair,eq7-10mm,gold,single,eq7-10mm,silver,single,eq7-10mm,rose gold,single,eq7-10mm,gold,pair,eq7-12mm,silver,single,eq7-12mm,rose gold,single,eq7-12mm,silver,pair,eq7-5mm+6mm,silver,single,eq7-6mm+7mm,gold,single,eq7-6mm+7mm,silver,single,eq7-6mm+7mm,rose gold,single,eq7-7mm+8mm,gold,single,eq7-7mm+8mm,silver,single,eq7-8mm+10mm,gold,single,eq7-10mm+12mm,gold,single,eq7-10mm+12mm,silver,single,eq7-10mm+12mm,rose gold,single,eq7-5+6+7mm,gold,single,eq7-5+6+7mm,rose gold,single,eq7-6+7+8mm,silver,single,eq7-6+7+8mm,rose gold,single,eq7-8+10+12mm,gold,single,eq7-8+10+12mm,silver,singleeq7-5mm,gold,single,
3eq7-5mm,rose gold,pair,
4eq7-6mm,gold,single,
5eq7-6mm,rose gold,single,
6eq7-6mm,silver,pair,
7eq7-6mm,rose gold,pair,
8eq7-7mm,silver,single,
9eq7-7mm,gold,pair,
10eq7-7mm,rose gold,pair,
11eq7-8mm,gold,single,
12eq7-8mm,silver,single,
13eq7-10mm,silver,pair,
14eq7-10mm,rose gold,pair,
15eq7-12mm,gold,single,
16eq7-12mm,gold,pair,
17eq7-12mm,rose gold,pair,
18eq7-5mm+6mm,gold,single,
19eq7-5mm+6mm,rose gold,single,
20eq7-7mm+8mm,rose gold,single,
21eq7-8mm+10mm,silver,single,
22eq7-8mm+10mm,rose gold,single,
23eq7-5+6+7mm,silver,single,
24eq7-6+7+8mm,gold,single,
25eq7-7+8+10mm,gold,single,
26eq7-7+8+10mm,silver,single,
27eq7-7+8+10mm,rose gold,single,
28eq7-8+10+12mm,rose gold,single,
29eq7-5mm,silver,single,
30eq7-5mm,rose gold,single,
Data
Cell Formulas
RangeFormula
B2:B64B2="eq"&TEXTSPLIT(A2,,"eq",1)
Dynamic array formulas.
 
Upvote 1
Hi all! Unfortunately, not all SKUs have "eq" at the beginning. Here are a few more as an example.

I can simply change the formulas/code to account for all prefix versions but wanted to see if there is anything that I am able to do to account for all.

Also, thank you very much. This will help me immensely in the future as this isn't the only data that suffers from this! MrExcel comes to the rescue again.

J16-4US-white cz,gold,J16-4US-white cz,silver,J16-4US-white cz,rose gold,J16-4US-black cz,gold,J16-4US-black cz,silver,J16-4US-black cz,rose gold,J16-5US-white cz,silver,J16-5US-white cz,rose gold,J16-5US-black cz,silver,J16-6US-white cz,gold,J16-6US-white cz,silver,J16-6US-white cz,rose gold,J16-7US-white cz,gold,J16-7US-white cz,silver,J16-7US-black cz,gold,J16-7US-black cz,silver,J16-8US-white cz,gold,J16-8US-white cz,rose gold,J16-8US-black cz,silver,J16-5US-white cz,gold,J16-5US-black cz,gold,J16-5US-black cz,rose gold,J16-6US-black cz,gold,J16-6US-black cz,silver,J16-6US-black cz,rose gold,J16-7US-white cz,rose gold,J16-7US-black cz,rose gold,J16-8US-white cz,silver,J16-8US-black cz,gold,J16-8US-black cz,rose gold
DD-5mm,gold,single,DD-5mm,gold,pair,DD-5mm,silver,pair,DD-5mm,rose gold,pair,DD-6mm,gold,single,DD-6mm,rose gold,single,DD-7mm,gold,single,DD-7mm,silver,single,DD-7mm,gold,pair,DD-7mm,silver,pair,DD-8mm,silver,single,DD-8mm,rose gold,single,DD-8mm,silver,pair,DD-10mm,silver,single,DD-10mm,rose gold,single,DD-10mm,gold,pair,DD-5mm+6mm,gold,single,DD-5mm+6mm,rose gold,single,DD-6mm+7mm,gold,single,DD-6mm+7mm,rose gold,single,DD-7mm+8mm,silver,single,DD-8mm+10mm,gold,single,DD-8mm+10mm,rose gold,single,DD-5+6+7mm,gold,single,DD-5+6+7mm,rose gold,single,DD-6+7+8mm,gold,single,DD-6+7+8mm,silver,single,DD-6+7+8mm,rose gold,single,DD-7+8+10mm,rose gold,single,DD-5mm,silver,single,DD-5mm,rose gold,single,DD-6mm,silver,single,DD-6mm,gold,pair,DD-6mm,silver,pair,DD-6mm,rose gold,pair,DD-7mm,rose gold,single,DD-7mm,rose gold,pair,DD-8mm,gold,single,DD-8mm,gold,pair,DD-8mm,rose gold,pair,DD-10mm,gold,single,DD-10mm,silver,pair,DD-10mm,rose gold,pair,DD-5mm+6mm,silver,single,DD-6mm+7mm,silver,single,DD-7mm+8mm,gold,single,DD-7mm+8mm,rose gold,single,DD-8mm+10mm,silver,single,DD-5+6+7mm,silver,single,DD-7+8+10mm,gold,single,DD-7+8+10mm,silver,single
 
Upvote 0
How about
Excel Formula: 
=LET(a,TEXTBEFORE(A2,"-"),a&TEXTSPLIT(A2,,a))
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

R
  • Solved
MATCH using the first value found and skipping all the rest
Replies
4
Views
399
Fluff
Fluff
C
  • Solved
Using Xmatch to look at multiple columns to extract list?
Replies
5
Views
1K
Coyotex3
C
J
  • Question Question
Populate Multiple Columns from Several Criteria
Replies
0
Views
303
JamesRo
J
F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
257
frewert
F
J
  • Question Question
Formula is changing after getting new data?
Replies
7
Views
477
JamesRo
J

Forum statistics

Threads
1,226,114
Messages
6,189,052
Members
453,522
Latest member
Seeker2025

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top