In the Question table, sales information including Date, Product Name, and Quantity is provided without any separators. Separate these columns as shown in the result table.
📌 Challenge Details and Links
Challenge Number: 63
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Custom Splitter! with Power Query
Power Query solution 1 for Custom Splitter!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Info],
S = Table.FromRows(
List.Transform(
Source,
each
let
d = Text.Select(_, {"A" .. "Z"})
in
{Text.BeforeDelimiter(_, d), d, Text.AfterDelimiter(_, d)}
),
{"Date", "Product", "Quantity"}
)
in
SPower Query solution 2 for Custom Splitter!, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "InfoRng"]}[Content],
Transform = Table.FromRecords(
Table.TransformRows(
Source,
each [
a = Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z"})([Column1]),
b = Splitter.SplitTextByCharacterTransition({"A" .. "Z"}, {"0" .. "9"})(List.Last(a)),
c = Record.FromList(
{List.First(a)} & b,
type [Date = text, Product = text, Quantity = text]
)
][c]
)
)
in
TransformPower Query solution 3 for Custom Splitter!, proposed by Brian Julius:
let
Source = DataRaw,
SplitDigitToNonDigit = Table.SplitColumn(
Source,
"Info",
Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)),
{"Info.1", "Info.2", "Info.3", "Info.4"}
),
SplitNonDigitToDigit = Table.SplitColumn(
SplitDigitToNonDigit,
"Info.4",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}),
{"Product", "Quantity"}
),
MergeDate = Table.CombineColumns(
SplitNonDigitToDigit,
{"Info.1", "Info.2", "Info.3"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"Date"
),
ReType = Table.TransformColumnTypes(MergeDate, {{"Date", type date}, {"Quantity", Int64.Type}})
in
ReTypePower Query solution 4 for Custom Splitter!, proposed by Cristobal Salcedo Beltran:
let
Source = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
#"Split Column by Character Transition1" = Table.SplitColumn(
Source,
"Info",
Splitter.SplitTextByCharacterTransition(
{"0" .. "9"},
(c) => not List.Contains({"0" .. "9", "/"}, c)
),
{"Date", "Product"}
),
#"Split Column by Character Transition2" = Table.SplitColumn(
#"Split Column by Character Transition1",
"Product",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}),
{"Product", "Quantity"}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Split Column by Character Transition2",
{{"Date", type date}, {"Quantity", Int64.Type}}
)
in
#"Changed Type"Power Query solution 5 for Custom Splitter!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
tab = Table.TransformColumns(
Fonte,
{
"Info",
each Table.FromRows(
{
let
a = Text.Select(_, {"A" .. "Z"}),
b = Text.BeforeDelimiter(_, a),
c = Text.AfterDelimiter(_, a)
in
{b} & {a} & {c}
},
{"Date", "Product", "Quntity"}
)
}
)[Info],
res = Table.Combine(tab)
in
resPower Query solution 6 for Custom Splitter!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
tab = Table.TransformColumns(Fonte, {"Info", each let
cab = {"Product","Date","Quntity"},
a = Text.Select(_,{"A".."Z"}),
b = List.Select(Text.Split(List.Accumulate({"A".."Z"},_,(s,c)=> Text.Replace(s,c,"|")),"|"), each _ <> "")
in Table.SelectColumns(Table.FromRows({{a} & b},cab),List.Sort(cab) )})[Info],
res = Table.Combine(tab)
in
resPower Query solution 7 for Custom Splitter!, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SP = Splitter.SplitTextByCharacterTransition,
T = Table.AddColumn,
Fx = (x)=> let
A = x,
B = SP({"0".."9"},{"A".."Z"})(A),
C = List.Transform(B, SP({"A".."Z"},{"0".."9"})),
D = Table.FromRows({List.Combine(C)}),
E = Table.TransformColumnTypes(D,{"Column1",type date}),
F = Table.ToRows(E){0}
in F,
G = T(S,"Date", each Fx([Info]){0}),
H = T(G,"Product", each Fx([Info]){1}),
Sol = T(H,"Quantity", each Fx([Info]){2})
in
SolPower Query solution 8 for Custom Splitter!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.FromList(
Source[Info],
each [
t = Text.Select(_, {"A" .. "Z"}),
r = {Date.From(Text.BeforeDelimiter(_, t)), t, Number.From(Text.AfterDelimiter(_, t))}
][r],
type table [Date = date, Product = text, Qty = number]
)
in
ReturnPower Query solution 9 for Custom Splitter!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
Split = Table.Combine(Table.AddColumn(Source, "A", each
let
a = {"0".."9"}, b = {"A".."Z"},
c = Splitter.SplitTextByCharacterTransition,
d = c(a,b)([Info]),
e = List.Combine(List.Transform(d, each c(b,a)(_))),
f = Table.FromRows({e}, {"Date", "Product", "Quantity"})
in f)[A])
in
SplitPower Query solution 10 for Custom Splitter!, proposed by Mahmoud Bani Asadi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformColumnTypes(
Table.Combine(
Table.AddColumn(
Source,
"Custom",
each [
fn = Splitter.SplitTextByCharacterTransition,
a = fn({"0" .. "9"}, {"A" .. "Z"})([Info]),
b = fn({"A" .. "Z"}, {"0" .. "9"})(a{1}),
c = #table({"Date", "Product", "Quantity"}, {{a{0}, b{0}, b{1}}})
][c]
)[Custom]
),
{{"Date", type date}, {"Product", type text}, {"Quantity", Int64.Type}}
)
in
TransformPower Query solution 11 for Custom Splitter!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"P1",
each Table.Transpose(
Table.FromList(
Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z"})([Info]),
null
),
{"Date", "Other"}
)
),
#"Expanded P1" = Table.ExpandTableColumn(
#"Added Custom",
"P1",
{"Date", "Other"},
{"Date", "Other"}
),
#"Split Column by Character Transition" = Table.SplitColumn(
#"Expanded P1",
"Other",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}),
{"Other.1", "Other.2"}
),
#"Renamed Columns" = Table.RenameColumns(
#"Split Column by Character Transition",
{{"Other.1", "Product"}, {"Other.2", "Qty"}}
),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns", {"Info"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns", {{"Date", type date}})
in
#"Changed Type"Power Query solution 12 for Custom Splitter!, proposed by Daniel Madhadha:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Split" = Table.SplitColumn(
Source,
"Info",
Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)),
{"Info.1", "Info.2", "Info.3", "Info.4"}
),
#"Split1" = Table.SplitColumn(
#"Split",
"Info.4",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}),
{"Info.4.1", "Info.4.2"}
),
#"Replaced Value" = Table.ReplaceValue(
#"Split1",
"/",
"",
Replacer.ReplaceText,
{"Info.2", "Info.3"}
),
#"Merged Columns" = Table.CombineColumns(
#"Replaced Value",
{"Info.3", "Info.2", "Info.1"},
Combiner.CombineTextByDelimiter("/", QuoteStyle.None),
"Merged"
),
#"Changed Type" = Table.TransformColumnTypes(#"Merged Columns", {{"Merged", type date}}),
#"Ans" = Table.RenameColumns(
#"Changed Type",
{{"Merged", "Date"}, {"Info.4.1", "Product"}, {"Info.4.2", "Quantity"}}
)
in
#"Ans"Power Query solution 13 for Custom Splitter!, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.FromRows(
Table.AddColumn(
Source,
"c",
(x) =>
[
i = x[Info],
l = {"A" .. "Z"},
s = Text.PositionOfAny(i, l),
p = {0, s, s + Text.Length(Text.Select(i, {"A" .. "Z"}))},
r = Splitter.SplitTextByPositions(p)(i)
][r]
)[c],
type table [Date = text, Product = text, Quantity = text]
),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom", {{"Quantity", Int64.Type}})
in
#"Changed Type"Solving the challenge of Custom Splitter! with Excel
Excel solution 1 for Custom Splitter!, proposed by Bo Rydobon 🇹🇭:
=WRAPROWS(
REGEXEXTRACT(
CONCAT(
B3:B15&" "
),
"[d/]+|[A-Z]+",
1
),
3
)Excel solution 2 for Custom Splitter!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:B15,
a,
TEXTSPLIT(
z,
SEQUENCE(
10
)-1,
"/",
1
),
HSTACK(
TEXTSPLIT(
z,
a
),
a,
TEXTAFTER(
z,
a
)
)
)Excel solution 3 for Custom Splitter!, proposed by 🇰🇷 Taeyong Shin:
=LET(d,
B3:B15,
λ,
LAMBDA(
f,
[x],
f(
d,
CHAR(
SEQUENCE(
26,
,
65
)
),
x
)
),
dt,
λ(TEXTBEFORE),
qtr,
λ(TEXTAFTER,
-1),
HSTACK(
--dt,
MAP(
d,
LAMBDA(
x,
TEXTSPLIT(
x,
dt,
qtr,
1
)
)
),
--qtr
))Excel solution 4 for Custom Splitter!, proposed by محمد حلمي:
=LET(
d,
B3:B15,
s,
SEQUENCE(
26
), v,
MAP(
d,
LAMBDA(
b,
@TOCOL(
FIND(
CHAR(
s+64
),
b
),
2
)
)
), j,
MID(
d,
v,
9
),
i,
TEXTSPLIT(
j,
s-1
), HSTACK(
--LEFT(
d,
v-1
),
i,
--SUBSTITUTE(
j,
i,
)
)
)Excel solution 5 for Custom Splitter!, proposed by محمد حلمي:
=LET(
d,
B3:B15,
s,
SEQUENCE(
26
),
i,
TEXTBEFORE(
d,
CHAR(
s+64
)
), j,
TEXTSPLIT(
SUBSTITUTE(
d,
i,
),
s-1
),
HSTACK(
--i,
j,
--SUBSTITUTE(
d,
i&j,
)
)
)Excel solution 6 for Custom Splitter!, proposed by 🇵🇪 Ned Navarrete C.:
=TEXTSPLIT(
TEXTAFTER(
MAP(
B3:B15,
LAMBDA(
r,
LET(
c,
TEXTSPLIT(
r,
TEXTSPLIT(
r,
{"A",
"B",
"C"},
,
1
),
,
1
),
i,
"*",
i&TEXTBEFORE(
r,
c
)&i&c&i&TEXTAFTER(
r,
c
)
)
)
),
"*",
{1,
2,
3}
),
"*"
)Excel solution 7 for Custom Splitter!, proposed by Oscar Mendez Roca Farell:
=REDUCE(
D2:F2,
B3:B15,
LAMBDA(
i,
x,
LET(
n,
TEXTSPLIT(
x,
CHAR(
64+ROW(
1:26
)
),
,
1
),
VSTACK(
i,
HSTACK(
@--n,
TEXTSPLIT(
x,
n,
,
1
),
MIN(
--n
)
)
)
)
)
)Excel solution 8 for Custom Splitter!, proposed by Julian Poeltl:
=LET(
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
B3:B15,
LAMBDA(
I,
LET(
D,
TAKE(
TEXTBEFORE(
I,
"/",
2
)&"/"&TEXTSPLIT(
TEXTAFTER(
I,
"/",
2
),
CHAR(
SEQUENCE(
26
)+64
)
),
,
1
),
P,
TAKE(
TEXTSPLIT(
TEXTAFTER(
I,
D
),
SEQUENCE(
9
)
),
,
1
),
Q,
TEXTAFTER(
I,
P
),
TEXTJOIN(
",",
,
D,
P,
Q
)
)
)
)
),
","
),
3
),
IFERROR(
R*1,
R
)
)Excel solution 9 for Custom Splitter!, proposed by Kris Jaganah:
=LET(
a,
B3:B15,
b,
REGEXEXTRACT(
a,
"[A-Z]+"
),
VSTACK(
{"Date",
"Product",
"Quantity"},
HSTACK(
TEXTBEFORE(
a,
b
),
b,
TEXTAFTER(
a,
b
)
)
)
)Excel solution 10 for Custom Splitter!, proposed by Mahmoud Bani Asadi:
=LET(
a,
B3:B15,b,
REGEXEXTRACT(
a,
"d+/d+/d+"
),c,
REGEXEXTRACT(
a,
"d+(D+)d+$",
2
),d,
REGEXEXTRACT(
a,
"d+$"
),HSTACK(
--b,
c,
--d
)Excel solution 11 for Custom Splitter!, proposed by Sunny Baggu:
=REDUCE( {"Date",
"Product",
"Quantity"}, B3:B15, LAMBDA(
a,
v, VSTACK(
a,
LET(
ts,
TEXTSPLIT(
v,
,
CHAR(
SEQUENCE(
26,
,
65
)
),
1
),
a,
TAKE(
ts,
1
),
b,
TAKE(
ts,
-1
),
c,
TEXTBEFORE(
TEXTAFTER(
v,
a
),
b
),
HSTACK(
a,
c,
b
)
)
) ))Excel solution 12 for Custom Splitter!, proposed by Asheesh Pahwa:
=LET(
I,
B3:B15,
REDUCE(
D2:F2,
I,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
alp,
CHAR(
SEQUENCE(
26
)+64
),
t,
TEXTSPLIT(
y,
alp
),
m,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
xm,
ISNUMBER(
XMATCH(
m,
alp
)
),
f,
CONCAT(
FILTER(
m,
xm
)
),
HSTACK(
TAKE(
t,
,
1
),
f,
TAKE(
t,
,
-1
)
)
)
)
)
)
)Excel solution 13 for Custom Splitter!, proposed by Bilal Mahmoud kh.:
=HSTACK( MAP(
A3:A15,
LAMBDA(
x,
TAKE(
TEXTSPLIT(
x,
CHAR(
SEQUENCE(
26,
,
65,
1
)
),
,
TRUE
),
,
1
)
)
), MAP(
A3:A15,
LAMBDA(
x,
DROP(
TEXTSPLIT(
x,
CHAR(
SEQUENCE(
9,
,
49,
1
)
),
,
TRUE
),
,
3
)
)
), MAP(
A3:A15,
LAMBDA(
x,
DROP(
TEXTSPLIT(
x,
CHAR(
SEQUENCE(
26,
,
65,
1
)
),
,
TRUE
),
,
1
)
)
))Excel solution 14 for Custom Splitter!, proposed by Diarmuid Early:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
REGEXREPLACE(
B3:B15,
"[A-Z]+",
"-$0-"
)
),
"-",
"|"
)
The REPLACE adds a "-" before and after the letters,
then TEXTJOIN combines them all into one big string split by "|",
and finally TEXTSPLIT splits it into a 2-D array based on those two delimiters.
I'd love to see the new REGEX functions be a little more array friendly so you could REGEXEXTRACT all capture groups across rows from a column of inputs (similar to Owen's Python in Excel solution)Excel solution 15 for Custom Splitter!, proposed by Hamidi Hamid:
=HSTACK(
TEXTSPLIT(
B3:B15,
CHAR(
SEQUENCE(
26,
,
65,
1
)
), ),
LET(
x,
MID(
B3:B15,
LEN(
TEXTSPLIT(
B3:B15,
CHAR(
SEQUENCE(
26,
,
65,
1
)
), )
)+1,
2
),
TEXTSPLIT(
x,
CHAR(
SEQUENCE(
9,
,
49,
1
)
),
,
1
)
),
IF(
ISERROR(
RIGHT(
B3:B15,
2
)*1
),
RIGHT(
B3:B15,
1
),
RIGHT(
B3:B15,
2
)
)*1
)Excel solution 16 for Custom Splitter!, proposed by Hazem Hassan:
=LET(
R,
B3:B15,
m,
MAP(
R,
LAMBDA(
x,
CONCAT(
TOCOL(
IF(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)>"9",
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
1/0
),
3
)
)
)
),
HSTACK(
TEXTBEFORE(
R,
m
),
m,
TEXTAFTER(
R,
m
)
)
)
//
=LET(
rn,
B3:B15,
rx,
REGEXEXTRACT(
rn,
"[A-Z]+"
),
HSTACK(
TEXTBEFORE(
rn,
rx
),
rx,
TEXTAFTER(
rn,
rx
)
)
)Excel solution 17 for Custom Splitter!, proposed by Hussein SATOUR:
=WRAPROWS(
REGEXEXTRACT(
CONCAT(
B3:B15&"|"
),
"(d+/d+/d+)|([A-Z]+)|(d+)",
1
),
3
)
Excel solution 18 for Custom Splitter!, proposed by Peter Bartholomew:
= {"^[d/]+","[A-Z]+","d+$"}
The result then relies upon the broadcasting of scalar text values using
= REGEXEXTRACT(info, regexArr)</