Find the substrings of given numbers which are Palindromic. Substrings should be of at least 3 digits long. Ex. 31331 – 313 and 1331 are Palindromic substrings in this having a lengths of at least 3.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 323
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find palindromic substrings with at least 3 digits with Power Query
Power Query solution 1 for Find palindromic substrings with at least 3 digits, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.AddColumn(
Source,
"Palindrome",
each
let
n = Text.From([Numbers]),
l = Text.Length(n),
t = List.Transform
in
List.Accumulate(
List.Combine(t({3 .. l}, each t({0 .. l - _}, (s) => Text.Middle(n, s, _)))),
"",
(s, c) => if c = Text.Reverse(c) then s & (if s = "" then s else ", ") & c else s
)
)
in
SPower Query solution 2 for Find palindromic substrings with at least 3 digits, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
T = Text.From([Numbers]),
C = Text.Length(T),
L = List.TransformMany(
{1 .. C},
(x) => {x + 2 .. C},
(x, y) =>
[
P = Text.Range(T, x - 1, y - x + 1),
TF = P = Text.Reverse(P),
C = if TF then P else null
][C]
),
R = Text.Combine(L, ", ")
][R]
)
in
ReturnPower Query solution 3 for Find palindromic substrings with at least 3 digits, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Palindrome",
each
let
a = Text.From([Numbers]),
b = List.Select({1 .. Text.Length(a)}, each _ > 2),
c = List.Transform(
b,
each List.Transform(
{0 .. Text.Length(a) - 1},
(x) => try Text.Range(a, x, _) otherwise null
)
),
d = List.RemoveNulls(List.Combine(c)),
e = Text.Combine(List.Select(d, each _ = Text.Reverse(_)), ", ")
in
e
)[[Palindrome]]
in
SolPower Query solution 4 for Find palindromic substrings with at least 3 digits, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
max = List.Max(List.Transform(Fonte[Numbers], each Text.Length(Text.From(_)))),
res = Table.AddColumn(
Fonte,
"Polindrome",
each [
a = List.Combine(
List.Transform(
{0 .. List.Count(Text.ToList(Text.From([Numbers])))},
(x) =>
List.Transform({3 .. max}, (y) => Text.Middle(Text.From([Numbers]), Number.From(x), y))
)
),
b = Text.Combine(
List.Select(List.Distinct(a), each Text.Length(_) >= 3 and _ = Text.Reverse(_)),
", "
)
][b]
)
in
resSolving the challenge of Find palindromic substrings with at least 3 digits with Excel
Excel solution 1 for Find palindromic substrings with at least 3 digits, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A8,LAMBDA(n,LET(l,LEN(n)-2,TEXTJOIN(", ",,UNIQUE(TOCOL(MAP(MID(n,SEQUENCE(l),SEQUENCE(,l,3)),LAMBDA(m,REPT(m,m=CONCAT(MID(m,16-SEQUENCE(15),1)))))))))))Excel solution 2 for Find palindromic substrings with at least 3 digits, proposed by Rick Rothstein:
=MAP(
A2:A8,
LAMBDA(
r,
LET(
f,
LAMBDA(
x,
x=CONCAT(
MID(
x,
LEN(
x
)-SEQUENCE(
LEN(
x
),
,
0
),
1
)
)
),
g,
LAMBDA(
s,
m,
DROP(
TEXTSPLIT(
REDUCE(
"",
SEQUENCE(
LEN(
s
)-m+1,
,
m
),
LAMBDA(
a,
x,
a&" "&TEXTJOIN(
" ",
,
MID(
s,
SEQUENCE(
LEN(
s
)-x+1
),
x
)
)
)
),
,
" "
),
1
)
),
TEXTJOIN(
", ",
,
IFERROR(
FILTER(
g(
r,
3
),
MAP(
g(
r,
3
),
LAMBDA(
x,
f(
x
)
)
)
),
""
)
)
)
)
)Excel solution 3 for Find palindromic substrings with at least 3 digits, proposed by John V.:
=MAP(A2:A8,LAMBDA(x,LET(s,ROW(1:10),TEXTJOIN(", ",,MAP(UNIQUE(TOCOL(--MID(x,s,TOROW(s)),2)),LAMBDA(z,REPT(z,(z=--CONCAT(MID(z,11-s,1)))*(z>99))))))))Excel solution 4 for Find palindromic substrings with at least 3 digits, proposed by محمد حلمي:
=MAP(
A2:A8,
LAMBDA(
c,
LET(
s,
SEQUENCE(
,
20
),
REDUCE(
"",
UNIQUE(
TOCOL(
MID(
c,
SEQUENCE(
LEN(
c
)-2
),
s+2
)
)
),
LAMBDA(
a,
d,
TEXTJOIN(
", ",
,
a,
REPT(
d,
d=CONCAT(
MID(
d,
21-s,
1
)
)
)
)
)
)
)
)
)Excel solution 5 for Find palindromic substrings with at least 3 digits, proposed by محمد حلمي:
=MAP(A2:A8,LAMBDA(c,LET(s,SEQUENCE(,20),TEXTJOIN(", ",,UNIQUE(TOCOL(MAP(MID(c,SEQUENCE(LEN(c)-2),s+2),LAMBDA(a,REPT(a,a=CONCAT(MID(a,21-s,1)))))))))))Excel solution 6 for Find palindromic substrings with at least 3 digits, proposed by محمد حلمي:
=MAP(A2:A8,
LAMBDA(c,
IFERROR(ARRAYTOTEXT(UNIQUE(TOCOL(MAP(MID(
c,
SEQUENCE(
LEN(
c
)-2
),
SEQUENCE(
,
20,
3
)
),
LAMBDA(a,
a/(a=CONCAT(
MID(
a,
21-SEQUENCE(
20
),
1
)
)))),
2))),
"")))Excel solution 7 for Find palindromic substrings with at least 3 digits, proposed by Kris Jaganah:
=MAP(A2:A8,
LAMBDA(y,
LET(a,
TOCOL(
MID(
y,
SEQUENCE(
LEN(
y
)
),
SEQUENCE(
,
LEN(
y
)
)
)
),
b,
MAP(
a,
LAMBDA(
x,
CONCAT(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
)
)
),
ARRAYTOTEXT(UNIQUE(FILTER(b,
(a=b)*(LEN(
b
)>2),
""))))))Excel solution 8 for Find palindromic substrings with at least 3 digits, proposed by Timothée BLIOT:
=MAP(
A2:A8,
LAMBDA(
z,
LET(
F,
LAMBDA(
n,
LET(
A,
LEN(
n
)/2,
B,
LEFT(
n,
A
),
D,
CONCAT(
MID(
RIGHT(
n,
A
),
SEQUENCE(
A,
,
A,
-1
),
1
)
),
D=B
)
),
A,
LEN(
z
),
TEXTJOIN(
", ",
1,
MAP(
SEQUENCE(
A-2,
,
3
),
LAMBDA(
x,
TEXTJOIN(
", ",
1,
MAP(
SEQUENCE(
A-x+1
),
LAMBDA(
y,
LET(
B,
MID(
z,
y,
x
),
IF(
F(
B
),
B,
""
)
)
)
)
)
)
)
)
)
)
)Excel solution 9 for Find palindromic substrings with at least 3 digits, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A8,
LAMBDA(
n,
LET(
l,
LEN(
n
),
IFERROR(
ARRAYTOTEXT(
UNIQUE(
TOCOL(
MAP(
TOCOL(
MID(
n,
SEQUENCE(
,
l-2
),
SEQUENCE(
l,
,
3
)
)
),
LAMBDA(
a,
--a/N(
a=CONCAT(
MID(
a,
15-SEQUENCE(
14
),
1
)
)
)
)
),
2
)
)
),
""
)
)
)
)Excel solution 10 for Find palindromic substrings with at least 3 digits, proposed by Sunny Baggu:
=MAP(
A2:A8,
LAMBDA(n,
LET(
_a,
UNIQUE(
TOCOL(
MID(
n,
SEQUENCE(
LEN(
n
)
),
SEQUENCE(
,
LEN(
n
)
)
)
)
),
_b,
FILTER(
_a,
LEN(
_a
) > 2
),
_e1,
LAMBDA(
x,
CONCAT(
MID(
x,
LEN(
x
) + 1 - SEQUENCE(
LEN(
x
)
),
1
)
)
),
_c,
MAP(_b,
LAMBDA(y,
_e1(y))),
ARRAYTOTEXT(
FILTER(
_b,
_b = _c,
""
)
)
)
)
)Excel solution 11 for Find palindromic substrings with at least 3 digits, proposed by Md. Zohurul Islam:
=MAP(
A2:A8,
LAMBDA(
z,
LET(
u,
UNIQUE(
TOCOL(
MID(
z,
SEQUENCE(
LEN(
z
)
),
{3,
4,
5,
6,
7,
8,
9}
)
)
),
v,
FILTER(
u,
LEN(
u
& )>2
),
w,
MAP(
v,
LAMBDA(
x,
LET(
sq,
SEQUENCE(
LEN(
x
)
),
CONCAT(
SORTBY(
MID(
x,
sq,
1
),
sq,
-1
)
)
)
)
),
j,
ARRAYTOTEXT(
FILTER(
v,
v=w,
""
)
),
j
)
)
)Excel solution 12 for Find palindromic substrings with at least 3 digits, proposed by Charles Roldan:
=LET(
isPal,
LAMBDA(
g,
g(
g
)
)(LAMBDA(g,
LAMBDA(x,
IF(LEN(
x
) < 2,
TRUE,
IF(LEFT(
x
) = RIGHT(
x
),
g(
g
)(MID(
x,
2,
LEN(
x
) - 2
))))))),
Mp,
LAMBDA(
f,
LAMBDA(
x,
MAP(
x,
f
)
)
),
Fl,
LAMBDA(
f,
LAMBDA(
x,
FILTER(
x,
f(
x
),
""
)
)
),
Mp(LAMBDA(x,
ARRAYTOTEXT(LET(k,
SEQUENCE(
LEN(
x
)
),
Fl(
Mp(
isPal
)
)(DROP(
REDUCE(
"",
DROP(
k,
-2
),
LAMBDA(
a,
j,
VSTACK(
a,
MID(
x,
j,
2 + DROP(
k,
-j - 1
)
)
)
)
),
1
))))))
)(A2:A8)Excel solution 13 for Find palindromic substrings with at least 3 digits, proposed by Charles Roldan:
=LET(
Mp,
LAMBDA(
f,
LAMBDA(
x,
MAP(
x,
f
)
)
),
Fl,
LAMBDA(
f,
LAMBDA(
x,
FILTER(
x,
f(
x
),
""
)
)
),
OnlyPals,
Fl(Mp(LAMBDA(
g,
g(
g
)
)(LAMBDA(g,
LAMBDA(x,
IF(LEN(
x
) < 2,
TRUE,
IF(LEFT(
x
) = RIGHT(
x
),
g(
g
)(MID(
x,
2,
LEN(
x
) - 2
))))))))),
MinLen,
LAMBDA(
n,
Fl(
LAMBDA(
x,
LEN(
x
) >= n
)
)
),
Mp(LAMBDA(x,
ARRAYTOTEXT(OnlyPals(MinLen(
3
)(LET(
k,
SEQUENCE(
LEN(
x
)
),
UNIQUE(
TOCOL(
MID(
x,
k,
TOROW(
k
)
)
)
)
))))))
)(A2:A8)Excel solution 14 for Find palindromic substrings with at least 3 digits, proposed by Pieter de Bruijn:
=BYROW(MAP(DROP(
REDUCE(
0,
A2:A8,
LAMBDA(
a,
b,
VSTACK(
a,
UNIQUE(
TOROW(
MID(
b,
SEQUENCE(
,
1+LEN(
b
)-3
),
SEQUENCE(
1+LEN(
b
)-3,
,
3
)
),
,
1
),
1
)
)
)
),
1
),
LAMBDA(m,
m/(CONCAT(
MID(
m,
SEQUENCE(
LEN(
m
),
,
LEN(
m
),
-1
),
1
)
)=m))),
LAMBDA(
r,
IFERROR(
TEXTJOIN(
",",
,
TOROW(
r,
2
)
),
""
)
))Excel solution 15 for Find palindromic substrings with at least 3 digits, proposed by Giorgi Goderdzishvili:
=
MAP(A2:A8,
LAMBDA(y,
LET(
nm,
y,
ln,
LEN(
nm
),
mkr,
MAKEARRAY(
ln-2,
ln,
LAMBDA(
r,
c,
MID(
nm,
r,
c+2
)
)
),
tcU,
UNIQUE(
TOCOL(
mkr
)
),
mp,
MAP(tcU,
LAMBDA(x,
SUM( -- (MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
)=MID(
x,
SEQUENCE(
,
LEN(
x
),
LEN(
x
),
-1
),
1
)))=LEN(
x
))),
IFERROR(
TEXTJOIN(
", ",
,
FILTER(
tcU,
mp
)
),
""
))))Excel solution 16 for Find palindromic substrings with at least 3 digits, proposed by Edwin Tisnado:
=MAP(
A2:A8,
LAMBDA(
t,
LET(
s,
LEN(
t
),
l,
SEQUENCE(
s-2
),
TEXTJOIN(
", ",
1,
MAP(
l,
LAMBDA(
x,
CONCAT(
UNIQUE(
MAP(
l,
LAMBDA(
y,
LET(
z,
MID(
t,
x,
y+2
),
IF(
z=CONCAT(
SORTBY(
MID(
z,
SEQUENCE(
s
),
1
),
SEQUENCE(
s,
,
s,
-1
)
)
),
z,
)
)
)
)
)
)
)
)
)
)
)
)Excel solution 17 for Find palindromic substrings with at least 3 digits, proposed by Abdelrahman Omer, MBA, PMP:
=IFERROR(MAP(A2:A8,LAMBDA(a,LET(B,UNIQUE(TOCOL(MID(a,SEQUENCE(LEN(a)),SEQUENCE(,LEN(a)-2,3)))),ARRAYTOTEXT(FILTER(B,--MAP(B,LAMBDA(x,LET(z,ROUNDDOWN(LEN(x)/2,0),IFERROR(z=SUM(--(MID(x,SEQUENCE(z),1)=MID(x,LEN(x)-SEQUENCE(z)+1,1))),0))))))))),"")Excel solution 18 for Find palindromic substrings with at least 3 digits, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A8,
LAMBDA(c,
IFERROR(ARRAYTOTEXT(UNIQUE(TOCOL(MAP(MID(
c,
SEQUENCE(
LEN(
c
)-2
),
SEQUENCE(
,
LEN(
c
)-2,
3
)
),
LAMBDA(a,
a/(a=CONCAT(
MID(
a,
LEN(
a
)+1-SEQUENCE(
LEN(
a
)
),
1
)
)))),
2))),
"")))Excel solution 19 for Find palindromic substrings with at least 3 digits, proposed by Hazem Hassan:
=MAP(A2:A8,LAMBDA(y,LET(w,LEN(y),a,UNIQUE(TOCOL(MID(y,SEQUENCE(w),SEQUENCE(,w,3)))),IFERROR(ARRAYTOTEXT(FILTER(a,(a=MAP(a,LAMBDA(x,CONCAT(MID(x,SORT(SEQUENCE(LEN(x)),,-1),1)))))*(LEN(a)>=3)=1)),""))))Excel solution 20 for Find palindromic substrings with at least 3 digits, proposed by Kriddakorn Pongthanisorn:
=MAP(A2:A8,LAMBDA(x, LET( l, LEN(x),r,SEQUENCE(l),c, SEQUENCE(1,l-2,3),m,TOCOL(--MID(x,r,c)),o,MAP(m,LAMBDA(ar, ar=--CONCAT(MID(ar,SEQUENCE(LEN(ar),1,LEN(ar),-1),1)))),IFERROR(TEXTJOIN(", ",1,TOCOL(UNIQUE(FILTER(IF(o,m,y),m>100*o=TRUE)),3)),""))))Solving the challenge of Find palindromic substrings with at least 3 digits with Python in Excel
Python in Excel solution 1 for Find palindromic substrings with at least 3 digits, proposed by John V.:
Hi everyone!
One [Python] option could be:
n = str(s)
l = len(n) - 2
return ", ".join(k for k in [n[i:i + 3 + j] for j in range(l) for i in range(l - j)] if k == k[::-1])
[f(i) for i in xl("A2:A8")[0]]
Solving the challenge of Find palindromic substrings with at least 3 digits with R
R solution 1 for Find palindromic substrings with at least 3 digits, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(stringi)
input = read_excel("Substring Palindromes.xlsx", range = "A1:A8")
test = read_excel("Substring Palindromes.xlsx", range = "B1:B8")
is_palindrome <- function(s) {
s == stri_reverse(s)
}
generate_substrings <- function(s) {
n <- nchar(s)
positions <- crossing(start = 1:(n-2), end = 3:n) %>%
filter(end > start, end - start >= 2)
substrings <- pmap_chr(positions, ~ substr(s, ..1, ..2))
palindromic_substrings <- substrings[map_lgl(substrings, is_palindrome)]
palindromic_substrings
}
result = input %>%
rowwise() %>%
mutate(
substrings = map(Numbers, generate_substrings),
final = paste(substrings, collapse = ", ")) %>%
select(Palindrome = final) %>%
mutate(Palindrome = if_else(Palindrome == "", NA, Palindrome)) %>%
ungroup()
Solving the challenge of Find palindromic substrings with at least 3 digits with DAX
DAX solution 1 for Find palindromic substrings with at least 3 digits, proposed by Zoran Milokanović:
DEFINE
VAR t = GENERATEALL(SELECTCOLUMNS(GENERATEALL(ADDCOLUMNS(Input, "Length", LEN(Input[Numbers])), GENERATESERIES(3, [Length])), "Numbers", [Numbers], "Length", [Length], "L", [Value]), GENERATESERIES(1, [Length] - [L] + 1))
VAR p = ADDCOLUMNS(t, "P", VAR p = MID([Numbers], [Value], [L])RETURNIF(p = CONCATENATEX(ADDCOLUMNS(GENERATESERIES(1, LEN(p)), "Index", LEN(p) - [Value] + 1), MID(p, [Index], 1)), p, BLANK()))
EVALUATE
SUMMARIZE(p, [Numbers], "Palindrome", VAR n = [Numbers]RETURNSUBSTITUTE(TRIM(CONCATENATEX(FILTER(p, [Numbers] = n), [P], " ")), " ", ", "))
&&