Prepare the triangle from column A strings. Row 1 will have 1st letter, Row 2 will have next 2 letters, Row 3 will have next 3 letters….Last row will have all remaining letters.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 401
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Form Pyramid from Text String with Power Query
Power Query solution 1 for Form Pyramid from Text String, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
R = (_, n) => if _ = {} then _ else {List.FirstN(_, n)} & @R(List.Skip(_, n), n + 1),
S = Table.FromColumns(List.Zip(R(Text.ToList(Source{0}[String]), 1)))
in
SPower Query solution 2 for Form Pyramid from Text String, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Table.SelectRows(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
each [String] <> null
),
Sol = Table.Combine(
Table.AddColumn(
Source,
"Answer",
each
let
a = [String],
b = Text.Length(a),
c = Text.ToList(a),
d = (x, y, z) =>
let
e = List.FirstN(x, y),
f = List.RemoveFirstN(x, y),
g = z & {e}
in
if List.Count(List.Combine(g)) = b then g else @d(f, y + 1, g),
h = d(c, 0, {}),
i = Table.FromColumns(List.Zip(h))
in
i
)[Answer]
)
in
SolPower Query solution 3 for Form Pyramid from Text String, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
fx = (x) =>
List.Transform(
List.Transform({0 .. Number.RoundUp(List.Count({0 .. Text.Length(x)}) / 2)}, each {0 .. _}),
each {List.Sum(if List.Count(_) = 1 then {0} else List.RemoveLastN(_, 1))} & {List.Last(_)}
),
res = List.Select(
List.Transform(fx(Fonte{7}[String]), (x) => Text.Middle(Fonte{7}[String], x{0}, x{1})),
each _ <> ""
)
in
resPower Query solution 4 for Form Pyramid from Text String, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
a = S[String]{0},
b = Text.ToList(a),
c = List.Generate(
() => [i = 0, j = 1],
each [i] < List.Count(b),
each if [j] = List.Count(b) then [i = [i] + 1, j = [i] + 2] else [i = [i] + [j], j = [j] + 1],
each List.Range(b, [i], [j])
),
Sol = Table.FromColumns(List.Zip(c))
in
SolSolving the challenge of Form Pyramid from Text String with Excel
Excel solution 1 for Form Pyramid from Text String, proposed by Bo Rydobon 🇹🇭:
=LET(a,
A2,
i,
SEQUENCE(EVEN((1+LEN(
a
)*8)^0.5-1)/2),
j,
TOROW(
i
),
IF(iExcel solution 2 for Form Pyramid from Text String, proposed by Rick Rothstein:
=LET(z,
A9,
s,
SCAN("",
SEQUENCE(
99,
,
0
),
LAMBDA(a,
x,
MID(z,
1+x*(x+1)/2,
x+1))),
m,
MAX(
LEN(
s
)
),
MAKEARRAY(m+(m*(m+1)/2Excel solution 3 for Form Pyramid from Text String, proposed by John V.:
=LET(s,
SEQUENCE(-INT((1-(1+8*LEN(
A9
))^0.5)/2)),
t,
TOROW(
s
),
IF(sExcel solution 4 for Form Pyramid from Text String, proposed by محمد حلمي:
=LET(
a,
A2,
s,
SEQUENCE(
LEN(
a
)/2+1
)-1,
v,
TOROW(
s
),
IFERROR(
MID(
a,
IF(
s>=v,
SCAN(
1,
s,
LAMBDA(
a,
d,
a+d
)
)+v
),
1
),
""
)
)
For remove blank rows
=LET(
a,
A19,
s,
SEQUENCE(
LEN(
a
)/2+1
)-1,
v,
TOROW(
s
),
x,
SCAN(
1,
s,
LAMBDA(
a,
d,
a+d
)
),
e,
SUM(
N(
MID(
a,
x,
1
)>""
)
),
TAKE(
IFERROR(
MID(
a,
IF(
s>=v,
x+v
),
1
),
""
),
e,
e
)
)Excel solution 5 for Form Pyramid from Text String, proposed by Kris Jaganah:
=LET(a,
LEN(
A9
),
b,
SEQUENCE(
a
),
c,
MID(
A9,
b,
1
),
d,
((b+1)*b)/2,
e,
TOCOL(d/(d<=a),
3),
f,
VSTACK(
0,
DROP(
e,
-1
)
),
g,
--TEXTSPLIT(
TEXTJOIN(
"#",
0,
MAP(
e,
f,
LAMBDA(
x,
y,
ARRAYTOTEXT(
SEQUENCE(
,
x-y,
y+1
)
)
)
)
),
", ",
"#",
,
,
0
),
h,
XLOOKUP(
g,
b,
c,
""
),
i,
IFERROR(XLOOKUP(TOROW(b/(b>(MAX(
g
))),
3),
b,
c),
""),
j,
IFNA(
VSTACK(
h,
i
),
""
),
FILTER(
j,
TAKE(
j,
,
1
)<>""
))Excel solution 6 for Form Pyramid from Text String, proposed by Julian Poeltl:
=LET(String,
A2,
Size,
ROUNDUP((SQRT(
LEN(
String
)*8+1
)-1)/2,
0),
MAKEARRAY(Size,
Size,
LAMBDA(r,
c,
IF(r>=c,
MID(String,
r*(r-1)/2+c,
1),
""))))Excel solution 7 for Form Pyramid from Text String, proposed by Timothée BLIOT:
=LET(D,
LAMBDA(n,
n*(n+1)/2),
IFNA(
REDUCE(
LEFT(
A2
),
SEQUENCE(
ROWS(
TOCOL(
1/FLOOR(
LEN(
A2
)/D(
SEQUENCE(
99
)
),
1
),
3
)
)
),
LAMBDA(
w,
v,
VSTACK(
w,
MID(
A2,
SEQUENCE(
,
D(
v+1
)-D(
v
),
D(
v
)+1
),
1
)
)
)
),
""
))Excel solution 8 for Form Pyramid from Text String, proposed by Sunny Baggu:
=LET(
_s,
SEQUENCE(
LEN(
A9
)
),
_n,
DROP(
VSTACK(
1,
SCAN(
1,
_s,
LAMBDA(
a,
v,
a + v
)
)
),
-1
),
_s1,
MAKEARRAY(
ROWS(
_s
),
ROWS(
_s
),
LAMBDA(
r,
c,
INDEX(
SEQUENCE(
,
INDEX(
_s,
r,
1
),
INDEX(
_n,
r,
1
),
),
c
)
)
),
_r,
IFERROR(
MID(
A9,
_s1,
1
),
""
),
_r1,
FILTER(
_r,
TAKE(
_r,
,
1
) <> ""
),
FILTER(
_r1,
BYCOL(
N(
_r1 <> ""
),
LAMBDA(
a,
SUM(
a
)
)
)
)
)Excel solution 9 for Form Pyramid from Text String, proposed by Sunny Baggu:
=LET(
_s,
SEQUENCE(
LEN(
A14
)
),
_n,
DROP(
VSTACK(
1,
SCAN(
1,
_s,
LAMBDA(
a,
v,
a + v
)
)
),
-1
),
_s1,
MAKEARRAY(
ROWS(
_s
),
ROWS(
_s
),
LAMBDA(
r,
c,
INDEX(
SEQUENCE(
,
INDEX(
_s,
r,
1
),
INDEX(
_n,
r,
1
),
),
c
)
)
),
_r,
IFERROR(
MID(
A14,
_s1,
1
),
""
),
_r
)Excel solution 10 for Form Pyramid from Text String, proposed by LEONARD OCHEA 🇷🇴:
=LET(i,
A2,
l,
LEN(
i
),
u,
((1+8*l)^0.5-1)/2,
s,
SEQUENCE(
ROUNDUP(
u,
)
),
t,
(s^2-s+2)/2,
MID(
MID(
i,
t,
s
),
SEQUENCE(
,
u
),
1
))Excel solution 11 for Form Pyramid from Text String, proposed by 🇵🇪 Ned Navarrete C.:
=LET(l,
LEN(
A9
),
i,
ROUND((2*l)^0.5,
),
MAKEARRAY(i,
i,
LAMBDA(f,
c,
IF(c<=f,
MID(A9,
f*(f-1)/2+c,
1),
""))))
Removiendo columna extra en blanco.!!
=LET(l,
LEN(
A9
),
i,
ROUND((2*l)^0.5,
),
DROP(MAKEARRAY(i,
i,
LAMBDA(f,
c,
IF(c<=f,
MID(A9,
f*(f-1)/2+c,
1),
""))),
,
(i*(i+1)/2=l)-1))Excel solution 12 for Form Pyramid from Text String, proposed by Charles Roldan:
=LAMBDA(
f,
f(
f
)
)(LAMBDA(f,
LAMBDA(x,
[n],
LET(m,
n + 1,
y,
MID(
x,
SEQUENCE(
,
m
),
1
),
IFNA(IF(LEN(
x
) <= m,
y,
VSTACK(y,
f(
f
)(REPLACE(
x,
1,
m,
),
m))),
""))))
)(A19)Excel solution 13 for Form Pyramid from Text String, proposed by JvdV -:
=LET(i,ROW(1:99),r,MID(A2,SCAN(,i-1,SUM)+1,i),MID(FILTER(r,r<>""),SEQUENCE(,MAX(LEN(r))),1))Excel solution 14 for Form Pyramid from Text String, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=ARCHIVOMAKEARRAY(5;
5;
LAMBDA(x;
y;
SI(x>=y;
EXTRAE(A19;
y+(x^2-x)/2;
1);
"")))Excel solution 15 for Form Pyramid from Text String, proposed by Burhan Cesur:
hi ,
LET(
s,
SEQUENCE(
LEN(
A2
)
),
bc,
MID(
A2,
SCAN(
,
s-1,
SUM
)+1,
s
),
FILTER(
bc,
bc<>""
)
)Excel solution 16 for Form Pyramid from Text String, proposed by Tyler Cameron:
=LET(
a,
A2,
d,
LEN(
a
),
b,
MID(
a,
SEQUENCE(
d
),
1
),
e,
SQRT(
MIN(
IF(
SEQUENCE(
d
)^2>d+d,
SEQUENCE(
d
)^2,
""
)
)
),
MAKEARRAY(
e,
e,
LAMBDA(
r,
c,
IFERROR(
IF(
c>r,
"",
INDEX(
b,
IFERROR(
SUM(
SEQUENCE(
r-1
)
),
0
)+c
)
),
""
)
)
)
)Excel solution 17 for Form Pyramid from Text String, proposed by Alexandra Popoff:
= LAMBDA(Input,
// Text from a cell to be split
LET(z_In,
Input,
z_n,
LEN(
z_In
),
z_seq_i,
SEQUENCE(
z_n,
1,
0,
1
),
// Index base 0
z_seq_f,
SEQUENCE(
z_n,
1,
1,
1
),
// Index base 1
z_Sn_i,
SCAN(
& 0,
z_seq_i,
LAMBDA(
z_i,
z_t,
z_i+z_t
)
)+1,
// array start position
z_Sn_f,
SCAN(
0,
z_seq_f,
LAMBDA(
z_i,
z_t,
z_i+z_t
)
),
// array end position
z_row_max,
MAX(FILTER(z_seq_f,
(z_Sn_i<=z_n)*(z_n<=z_Sn_f))),
// number of row for the final array
z_text,
BYROW(
z_seq_f,
LAMBDA(
z_i,
RIGHT(
LEFT(
z_In,
z_i
),
1
)
)
),
// split text input into vertical array
z_text_array,
BYROW(SEQUENCE(
z_row_max,
1,
1,
1
),
LAMBDA(z_i,
TEXTJOIN("",
TRUE,
FILTER(z_text,
(INDEX(
z_Sn_i,
z_i,
1
)<=z_seq_f)*(z_seq_f<=INDEX(
z_Sn_f,
z_i,
1
)))))),
// Calc text by row
z_col_max,
MAX(
BYROW(
z_text_array,
LAMBDA(
t_i,
LEN(
t_i
)
)
)
),
// calc max of column the final array must have
z_Out,
MAKEARRAY(
z_row_max,
z_col_max,
LAMBDA(
z_x,
z_y,
IF(
z_y<=LEN(
INDEX(
z_text_array,
z_x,
1
)
),
RIGHT(
LEFT(
INDEX(
z_text_array,
z_x,
1
),
z_y
),
1
),
""
)
)
),
// Calc output array
z_Out)
)Solving the challenge of Form Pyramid from Text String with Python
Python solution 1 for Form Pyramid from Text String, proposed by Giorgi Goderdzishvili:
lst = ["BAG","BAGS","BAGGAGES","AQUAPHOBIA","NANOFABRICATION"]
for el in lst:
ln = len(el)
counter = 0
for i in range(1,ln+1):
for k in range(i):
if counter<=ln-1:
print(el[counter],end=' ')
counter+=1
print()
if counter>ln-1:
print()
break
&&