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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
275
Fluff
Fluff
J
  • Question
Populate Multiple Columns from Several Criteria
Replies
0
Views
219
JamesRo
J
J
  • Question
Formula is changing after getting new data?
Replies
7
Views
330
JamesRo
J
T
  • Question
Have two sets of user inputs. Need to check if any sequential *pairs* of input are replicated between the two sets, how can I do this?
Replies
1
Views
600
BigBeachBananas
B
T
  • Solved
Making a tough Excel Formula take one input as a range, and output as a Spill Array/Range.
Replies
5
Views
697
Fluff
Fluff

Forum statistics

Threads
1,215,212
Messages
6,123,653
Members
449,111
Latest member
ghennedy

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