Split the ID into two columns: The first column should contain all letters. The second column should contain all numbers.
📌 Challenge Details and Links
Challenge Number: 196
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Column Splitting! Part 6 with Power Query
Power Query solution 1 for Column Splitting! Part 6, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
_ = Table.SplitColumn(
Source,
"ID",
each {Text.Select(_, {"A" .. "Z"}), Text.Select(_, {"0" .. "9"})},
2
)
in
_Power Query solution 2 for Column Splitting! Part 6, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Intersects = Table.AddColumn(
Source,
"Custom",
each [
a = Text.ToList([ID]),
b = {"0" .. "9"},
c = {"A" .. "Z"},
d = Text.Combine(List.Select(a, each List.PositionOf(c, _) >= 0), ""),
e = Text.Combine(List.Select(a, each List.PositionOf(b, _) >= 0), ""),
f = Table.FromColumns({{d}, {e}}, {"ID.1", "ID.2"})
][f]
),
Expand = Table.ExpandTableColumn(Intersects, "Custom", {"ID.1", "ID.2"}, {"ID.1", "ID.2"})
in
ExpandPower Query solution 3 for Column Splitting! Part 6, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.ToRows(S),
Fx = (x)=> let
b = List.Transform(x, each Text.Select(_,{"A".."z"})),
c = List.Transform(x, each Text.Select(_,{"0".."9"})),
d = Table.FromRows(List.Zip({b,c})),
e = Table.TransformColumnNames(d, each Text.Replace(_,"Column","ID."))
in e,
Sol = Table.Combine(List.Transform(a, each Fx(_)))
in
SolPower Query solution 4 for Column Splitting! Part 6, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
Sol = Table.Combine(Table.AddColumn(Origen, "A", each
let
a = Text.Select([ID], {"A".."Z"}),
b = Number.From(Text.Select([ID], {"0".."9"})),
c = {a,b},
d = Table.FromRows({c}, List.Transform({1..List.Count(c)},
each "ID."&Text.From(_)))
in d)[A])
in
SolPower Query solution 5 for Column Splitting! Part 6, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WcnRyNjQyVorViVbyNTH1swCzAsMtLV2DwEyjICMXMMPNzdfI0ADMDA42NApWio0FAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [ID = _t]
),
Result = Table.FromColumns(
{
List.Transform(Source[ID], each Text.Remove(_, {"0" .. "9"})),
List.Transform(Source[ID], each Text.Select(_, {"0" .. "9"}))
},
{"IC.1", "ID.2"}
)
in
ResultPower Query solution 6 for Column Splitting! Part 6, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(A, "ID.1", each Text.Select([ID], {"A" .. "Z"})),
C = Table.AddColumn(B, "ID.2", each Text.Remove([ID], {"A" .. "Z"}))
in
CPower Query solution 7 for Column Splitting! Part 6, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.FromList(
Source[ID],
each {Text.Select(_, {"A" .. "Z"}), Text.Select(_, {"0" .. "9"})},
{"ID.1", "ID.2"}
)
in
ResultPower Query solution 8 for Column Splitting! Part 6, proposed by Antriksh Sharma:
let
Source = Table,
Transform = Table.TransformRows(
Source,
each [
ID = [Question],
Text = Text.Combine(List.RemoveMatchingItems(Text.ToList([Question]), {"0" .. "9"})),
Number = Number.From(
Text.Combine(List.RemoveMatchingItems(Text.ToList([Question]), {"A" .. "Z", "a" .. "z"}))
)
]
),
Combine = Table.FromRecords(Transform, type table [ID = text, Text = text, Number = number])
in
CombinePower Query solution 9 for Column Splitting! Part 6, proposed by Fateme Esmaeili:
st column (Letters):
= Table.AddColumn(#"Changed Type", "Letters", each Text.Select([test],{"A".."Z","a".."z"}))
2nd column (digits):
= Table.AddColumn(#"Added Custom", "digits", each Text.Select([test],{"0".."9"}))Power Query solution 10 for Column Splitting! Part 6, proposed by Ibrahim SACCA:
let
SOURCE = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
QUESTION = Table.TransformColumnTypes(SOURCE,{{"ID", type text}}),
#"REMOVE_NUMBERS" = Table.AddColumn(QUESTION, "ID_LETTER", each Text.Remove([ID], {"0".."9"})), // REMOVE NUMBERS IN STRING
#"REMOVE_LETTERS" = Table.AddColumn(#"REMOVE_NUMBERS", "ID_NUMBER", each Text.Select([ID], {"0".."9"})) // REMOVE LETTERS IN STRING
in
#"REMOVE_LETTERS" // FINAL STEP
➡️ You can also use Power Query "Add a Column from Examples" functionality to do the task with GUI easily.Power Query solution 11 for Column Splitting! Part 6, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
TransformTable = Table.TransformColumns(
Source,
{},
each [
a = Text.Select(_, {"A" .. "Z", "a" .. "z"}),
b = Text.Select(_, {"0" .. "9"}),
c = {{a}} & {{b}},
d = Table.FromColumns(c, {"ID.1", "ID.2"})
][d]
),
CombineTables = Table.Combine(TransformTable[ID])
in
CombineTablesPower Query solution 12 for Column Splitting! Part 6, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom = Table.AddColumn(
Source,
"Custom",
each [ID.1 = Text.Select([ID], {"A" .. "Z"}), ID.2 = Text.Select([ID], {"0" .. "9"})]
),
Final = Table.RemoveColumns(Table.ExpandRecordColumn(Custom, "Custom", {"ID.1", "ID.2"}), {"ID"})
in
FinalSolving the challenge of Column Splitting! Part 6 with Excel
Excel solution 1 for Column Splitting! Part 6, proposed by 🇰🇷 Taeyong Shin:
=REGEXREPLACE(
B3:B8,
{"d",
"D"},)
=REDUCE(
"",
SEQUENCE(
9
),
LAMBDA(
a,
v,
LET(
c,
MID(
B3:B8,
v,
1
),
b,
c>"9",
a&REPT(
c,
HSTACK(
b,
1-b
)
)
)
)
)Excel solution 2 for Column Splitting! Part 6, proposed by Oscar Mendez Roca Farell:
=LET(
m,
MID(
B3:B8,
SEQUENCE(
,
6
),
1
),
G,
LAMBDA(
F,
BYROW(
IF(
F(
-m
),
m,
""
),
CONCAT
)
),
HSTACK(
G(
ISERR
),
G(
ISNUMBER
)
)
)Excel solution 3 for Column Splitting! Part 6, proposed by Kris Jaganah:
=REDUCE(
"ID."&{1,
2},
B3:B8,
LAMBDA(
v,
w,
VSTACK(
v,
LET(
a,
LAMBDA(
x,
CONCAT(
REGEXEXTRACT(
w,
x,
1
)
)
),
HSTACK(
a(
"[A-z+]"
),
a(
"[0-9+]"
)
)
)
)
)
)Excel solution 4 for Column Splitting! Part 6, proposed by Sunny Baggu:
=LET( _a,
MAP( B3:B8, LAMBDA(
t,
REDUCE(
t,
SEQUENCE(
10,
,
0
),
LAMBDA(
a,
v,
SUBSTITUTE(
a,
v,
""
)
)
)
) ), _b,
MAP( B3:B8, _a, LAMBDA(
a,
b,
CONCAT(
TEXTSPLIT(
a,
,
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
1
)
)
) ), HSTACK(
_a,
_b
))Excel solution 5 for Column Splitting! Part 6, proposed by Sunny Baggu:
=REDUCE( B2 & {".1",
".2"}, B3:B8, LAMBDA(
x,
y, VSTACK(
x,
LET(
_a,
TEXTSPLIT(
y,
,
SEQUENCE(
10,
,
0
),
1
),
_b,
TEXTSPLIT(
y,
,
_a,
1
),
HSTACK(
CONCAT(
_a
),
CONCAT(
_b
)
)
)
) ))Excel solution 6 for Column Splitting! Part 6, proposed by abdelaziz allam:
=DROP(
REDUCE(
"",
B3:B8,
LAMBDA(
a,
b,
VSTACK(
a,
LET(
x,
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
HSTACK(
CONCAT(
FILTER(
x,
NOT(
ISNUMBER(
--x
)
)
)
),
CONCAT(
FILTER(
x,
ISNUMBER(
--x
)
)
)
)
)
)
)
),
1
)Excel solution 7 for Column Splitting! Part 6, proposed by Alejandro Campos:
=REDUCE(
{"ID.1",
"ID.2"},
B3:B8,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
C,
CONCAT,
R,
REGEXEXTRACT,
a,
C(
R(
y,
"[A-Za-z]+",
1
)
),
n,
C(
R(
y,
"d+",
1
)
),
HSTACK(
a,
n
)
)
)
)
)Excel solution 8 for Column Splitting! Part 6, proposed by Andy Heybruch:
=TEXTSPLIT( ARRAYTOTEXT( BYROW(
B3:B8,
LAMBDA(
a,
CONCAT(
REGEXEXTRACT(
a,
"D",
1
)
)
&"|"&
CONCAT(
REGEXEXTRACT(
a,
"d",
1
)
)
)
)
),
"|",
", "
)Excel solution 9 for Column Splitting! Part 6, proposed by Asheesh Pahwa:
=REDUCE(
D2:E2,
B3:B8,
LAMBDA(
a,
x,
VSTACK(
a,
LET(
m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
I,
ISNUMBER(
--m
),
HSTACK(
CONCAT(
FILTER(
m,
I
)
),
CONCAT(
FILTER(
m,
NOT(
I
)
)
)
)
)
)
)
)Excel solution 10 for Column Splitting! Part 6, proposed by Asheesh Pahwa:
=LET(id,B3:B8,REDUCE(D2:E2,id,LAMBDA(x,y,VSTACK(x,LET(t,TEXTSPLIT(y,SEQUENCE(10,,0),,1),n,TEXTSPLIT(y,t,,1),HSTACK(CONCAT(t),CONCAT(n)))))))Excel solution 11 for Column Splitting! Part 6, proposed by Eddy Wijaya:
=LET( arr,
B3:B8, REDUCE(
D2:E2,
arr,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
sp,
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
nb,
ISNUMBER,
f,
LAMBDA(
I,
CONCAT(
FILTER(
sp,
I
)
)
),
HSTACK(
f(
NOT(
nb(
--sp
)
)
),
--f(
nb(
--sp
)
)
)
)
)
)
)
)Excel solution 12 for Column Splitting! Part 6, proposed by Fausto Bier:
=REDUCE(
D2:E2,
B3:B8,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
k,
LAMBDA(
v,
x,
CONCAT(
REGEXEXTRACT(
v,
x,
1
)
)
),
HSTACK(
k(
v,
"D"
),
k(
v,
"d"
)
)
)
)
)
)Excel solution 13 for Column Splitting! Part 6, proposed by ferhat CK:
=LET(
a,
MAP(
B3:B8,
LAMBDA(
i,
LET(
l,
LAMBDA(
x,
y,
CONCAT(
REGEXEXTRACT(
x,
y,
1
)
)
),
ARRAYTOTEXT(
l(
i,
"[A-Z]+"
)&"-"&l(
i,
"[d]+"
)
)
)
)
),
TEXTSPLIT(
ARRAYTOTEXT(
a
),
"-",
"; "
)
)Excel solution 14 for Column Splitting! Part 6, proposed by Hamidi Hamid:
=LET(s;
LAMBDA(
a;
TEXTJOIN(
;
1;
a
)
);
y;
MID(
B3:B8;
SEQUENCE(
;
20
);
1
);
x;
BYROW(
IFERROR(
y*1;
""
);
s
);
g;
BYROW(
IF(
ISNUMBER(
y*1
);
"";
y
);
s
);
HSTACK(
g;
x*1
)Excel solution 15 for Column Splitting! Part 6, proposed by Hussain Ali Nasser:
=DROP(
REDUCE(
"",
B3:B8,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
s,
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
c,
CONCAT,
l,
c(
IF(
ISNUMBER(
s + 0
),
"",
s
)
),
n,
c(
IFERROR(
--s,
""
)
),
HSTACK(
l,
n
)
)
)
)
),
1
)Excel solution 16 for Column Splitting! Part 6, proposed by Hussein SATOUR:
=REGEXREPLACE(
B3:B8,
{"d",
"D"},
""
)Excel solution 17 for Column Splitting! Part 6, proposed by Md. Zohurul Islam:
=LET(
z,
B3:B8,
sq,
SEQUENCE,
con,
CONCAT,
F,
FILTER,
e,
ISERROR,
I,
ISNUMBER, u,
MAP(
z,
LAMBDA(
x,
LET(
a,
MID(
x,
sq(
LEN(
x
)
),
1
),
con(
F(
a,
e(
ABS(
a
)
)
)
)
)
)
), v,
MAP(
z,
LAMBDA(
x,
LET(
a,
MID(
x,
sq(
LEN(
x
)
),
1
),
con(
F(
a,
I(
ABS(
a
)
)
)
)
)
)
), w,
VSTACK(
{"ID.1",
"ID.2"},
HSTACK(
u,
ABS(
v
)
)
), w
)Excel solution 18 for Column Splitting! Part 6, proposed by Meganathan Elumalai:
=REDUCE(
{"ID.1",
"ID.2"},
B3:B8,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
ts,
TEXTSPLIT,
c,
CONCAT,
x,
ts(
v,
SEQUENCE(
10,
,
0
),
,
1
),
HSTACK(
c(
x
),
c(
ts(
v,
x,
,
1
)
)
)
)
)
)
)Excel solution 19 for Column Splitting! Part 6, proposed by Nicolas Micot:
=LET(
_extractLetters;
LAMBDA(
l_text;
CONCAT(
REGEX.EXTRAIRE(
l_text;
"[a-zA-Z]+";
1
)
)
); _extractDigits;
LAMBDA(
l_text;
CONCAT(
REGEX.EXTRAIRE(
l_text;
"[0-9]+";
1
)
)+0
); _ids;
B3:B8; ASSEMB.H(
MAP(
_ids;
_extractLetters
);
MAP(
_ids;
_extractDigits
)
)
)Excel solution 20 for Column Splitting! Part 6, proposed by Pieter de B.:
=LET(
S,
SEQUENCE,
L,
LAMBDA(
x,
MAP(
B3:B8,
LAMBDA(
b,
CONCAT(
TEXTSPLIT(
b,
x
)
)
)
)
),
HSTACK(
L(
S(
10
)-1
),
L(
CHAR(
S(
26,
,
65
)
)
)
)
)_x000D_
Excel solution 21 for Column Splitting! Part 6, proposed by Rick Rothstein:
=REDUCE(
{"ID1",
"ID2"},
B3:B8,
LAMBDA(
a,
x,
LET(
f,
LAMBDA(
c,
n,
CONCAT(
TEXTSPLIT(
x,
CHAR(
SEQUENCE(
c,
,
n
)
)
)
)
),
VSTACK(
a,
HSTACK(
f(
10,
48
),
f(
26,
65
)
)
)
)Excel solution 21 for Column Splitting! Part 6, proposed by Rick Rothstein:
=REDUCE(
{"ID1",
"ID2"},
B3:B8,
LAMBDA(
a,
x,
LET(
f,
LAMBDA(
c,
n,
CONCAT(
TEXTSPLIT(
x,
CHAR(
SEQUENCE(
c,
,
n
)
)
)
)
),
VSTACK(
a,
HSTACK(
f(
10,
48
),
f(
26,
65
)
)
)
)