Create blocks based on the sets of text number strings. Only 3 rules to follow:
📌 Challenge Details and Links
Challenge Number: 11
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Create Number Blocks From a Text String with Power Query
Power Query solution 1 for Create Number Blocks From a Text String, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Table.TransformColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{},
each
let
a = List.Transform(Text.Split(_, ","), Number.From),
b = List.RemoveLastN(
List.Combine(
List.Transform(a, each if _ = 0 then {null} & {null} else List.Repeat({1}, _) & {null})
)
)
in
b
),
Sol = Table.FromColumns(
List.Transform(Table.ToColumns(Source), each _{0}),
Table.ColumnNames(Source)
)
in
SolSolving the challenge of Create Number Blocks From a Text String with Excel
Excel solution 1 for Create Number Blocks From a Text String, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
TEXTSPLIT(
C2,
","),
t,
TEXTJOIN(
" ",
,
IF(
-n,
REPT(
1,
n),
" ")),
TRIM(
MID(
t,
SEQUENCE(
LEN(
t)),
1)))Excel solution 2 for Create Number Blocks From a Text String, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
TEXTSPLIT(
C2,
","),
TEXTSPLIT(
TEXTJOIN(
" ",
0,
IF(
-n,
REPT(
"1 ",
n),
" ")),
,
" "))Excel solution 3 for Create Number Blocks From a Text String, proposed by Rick Rothstein:
=DROP(
TEXTSPLIT(
REDUCE(
"",
0+TEXTSPLIT(
B2,
","),
LAMBDA(
a,
x,
a&IF(
x,
"-",
"--")&REPT(
-1,
x))),
,
"-"),
2)Excel solution 4 for Create Number Blocks From a Text String, proposed by محمد حلمي:
=DROP(
DROP(
REDUCE(
0,
TEXTSPLIT(
D2,
","),
LAMBDA(
a,
d,
VSTACK(
a,
VSTACK(
IFERROR(
SEQUENCE(
d)^0,
""),
"")))),
1),
-1)Excel solution 5 for Create Number Blocks From a Text String, proposed by محمد حلمي:
=LET(i,
LAMBDA(b,
LET(v,
SCAN(
0,
b,
LAMBDA(
a,
d,
IF(
d,
a+1,
0))),
x,
FILTER(
DROP(
VSTACK(
0,
v),
-1),
v=0),
TEXTJOIN(",",
0,
FILTER(x,
1-(x=0)*(DROP(
VSTACK(
0,
x),
-1)=0))))),
HSTACK(
i(
B4:B22),
i(
C4:C10),
i(
D4:D16)))Excel solution 6 for Create Number Blocks From a Text String, proposed by Kris Jaganah:
=BYCOL(
B4:D21,
LAMBDA(
x,
LET(
a,
LEN(
SUBSTITUTE(
TEXTSPLIT(
TEXTJOIN(
"-",
0,
x),
,
"--"),
"-",
"")),
TEXTJOIN(
",",
,
IF(
a=0,
"",
a)))))Excel solution 7 for Create Number Blocks From a Text String, proposed by Oscar Mendez Roca Farell:
=LET(
_t,
--TEXTSPLIT(
B2,
,
", "),
MID(
CONCAT(
REPT(
1,
_t)&" "),
SEQUENCE(
SUM(
_t+1)-1),
1))Excel solution 8 for Create Number Blocks From a Text String, proposed by Abdallah Ally:
=LET(
a,
b2,
b,
TEXTSPLIT(
a,
","),
DROP(
REDUCE(
"",
b,
LAMBDA(
x,
y,
VSTACK(
x,
"",
IFERROR(
EXPAND(
1,
y,
,
1),
"")))),
2))Excel solution 9 for Create Number Blocks From a Text String, proposed by Abdallah Ally:
=LET(
a,
B2,
b,
TEXTSPLIT(
a,
","),
DROP(
REDUCE(
"",
b,
LAMBDA(
x,
y,
VSTACK(
x,
"",
IFERROR(
MAKEARRAY(
y,
1,
LAMBDA(
x,
y,
1)),
"")))),
2))Excel solution 10 for Create Number Blocks From a Text String, proposed by Bhavya Gupta:
=LET(
a,
TEXTSPLIT(
SUBSTITUTE(
","&D2,
",0",
",0,0"),
,
",",
1)*1,
b,
SEQUENCE(
,
MAX(
a)+1),
DROP(
TOCOL(
IFS(
b<=a,
1,
b=a+1,
"",
1,
1/0),
2),
-1))
=LET(
a,
TEXTSPLIT(
SUBSTITUTE(
","&D2,
",0",
",0,0"),
,
",",
1)*1,
IF(
ISNA(
XMATCH(
SEQUENCE(
SUM(
a+1)-1),
SCAN(
0,
a,
LAMBDA(
x,
y,
x+y+1)))),
1,
""))Excel solution 11 for Create Number Blocks From a Text String, proposed by Bhavya Gupta:
=LET(
a,
TEXTSPLIT(
B2,
,
",")*1,
IF(
ISNA(
XMATCH(
SEQUENCE(
SUM(
a+1)-1),
SCAN(
0,
a,
LAMBDA(
x,
y,
x+y+1)))),
1,
""))Excel solution 12 for Create Number Blocks From a Text String, proposed by Bhavya Gupta:
=LET(
a,
TEXTSPLIT(
B2,
,
",")*1,
b,
SEQUENCE(
,
MAX(
a)+1),
DROP(
TOCOL(
IFS(
b<=a,
1,
b=a+1,
"",
1,
1/0),
2),
-1))Excel solution 13 for Create Number Blocks From a Text String, proposed by JvdV -:
=TEXTSPLIT(
TEXTJOIN(
22,
0,
IFERROR(
REPT(
12,
TEXTSPLIT(
B2,
",")-1)&1,
"")),
,
2)Excel solution 14 for Create Number Blocks From a Text String, proposed by Owen Price:
=LET(
s,
TEXTSPLIT(
B2,
","),
r,
REDUCE(
"",
s,
LAMBDA(
a,
b,
a&";"&REPT(
1&";",
b))),
out,
TEXTSPLIT(
r,
";"),
DROP(
TRANSPOSE(
out),
1))Excel solution 15 for Create Number Blocks From a Text String, proposed by Mey Tithveasna:
=TEXTSPLIT(TEXTJOIN ("//",
,
IFERROR(
REPT(
1&"/",
TEXTSPLIT(
B2,
",")-1)&1,
"")),
,
"/")Excel solution 16 for Create Number Blocks From a Text String, proposed by Hazem Hassan:
=LET(
d,
TEXTSPLIT(
B3,
,
","),
a,
CONCAT(
REPT(
1,
d)&IF(
1*d=0,
" ",
" ")),
DROP(
MID(
a,
SEQUENCE(
LEN(
a)),
1),
-1))Excel solution 17 for Create Number Blocks From a Text String, proposed by Hazem Hassan:
=LET(
b,
"*",
a,
TEXTSPLIT(
D3,
,
","),
DROP(
TEXTSPLIT(
CONCAT(
MAP(
a,
LAMBDA(
x,
IFERROR(
CONCAT(
CHOOSEROWS(
SEQUENCE(
MAX(
1*a)),
SEQUENCE(
x))^0&b),
b)))&b),
,
b),
-2))Excel solution 18 for Create Number Blocks From a Text String, proposed by Ziad Ahmed:
=REDUCE(
TOCOL(
,
1),
SPLIT(
A1,
","),
LAMBDA(
a,
i,
{a;SEQUENCE(
i,
1,
1,
0);""}))