List all Palindromic phrases. A Palindromic phrase is that which is same when read from backwards. When you are reading from backwards, only English alphabets need to be considered. Don’t consider spaces, questions marks or any other character which is not English alphabet. Ex. No lemon, no melon
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 307
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List all Palindromic phrases with Power Query
Power Query solution 1 for List all Palindromic phrases, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
each
let
b = Text.Select(Text.Upper([Phrase]), {"A" .. "Z"})
in
b = Text.Reverse(b)
)
in
AnsPower Query solution 2 for List all Palindromic phrases, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.SelectRows(
Source,
each
let
s = Text.Select(Text.Upper([Phrase]), {"A" .. "Z"})
in
s = Text.Reverse(s)
)
in
SPower Query solution 3 for List all Palindromic phrases, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.SelectRows(Source, each
let
a = Text.Lower([Phrase]),
b = Text.Select(a, {"a".."z"}),
c = b = Text.Reverse(b)
in c)
in
Sol
Con Text.Remove
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.SelectRows(Source, each
let
a = Text.Lower(Text.Remove([Phrase], {" ", ",", "?", "!", ".", "'", "—"})),
b = a = Text.Reverse(a)
in b)
in
Sol
Power Query solution 4 for List all Palindromic phrases, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each Text.Select(Text.Lower([Phrase]), {"a" .. "z"})
= Text.Reverse(Text.Select(Text.Lower([Phrase]), {"a" .. "z"}))
)
in
resPower Query solution 5 for List all Palindromic phrases, proposed by Brian Julius:
let
Source = Table.PromoteHeaders(
Excel.CurrentWorkbook(){[Name = "rng"]}[Content],
[PromoteAllScalars = true]
),
AddCheckPalind = Table.AddColumn(
Source,
"CheckPalind",
each [
low = {"a" .. "z"},
a = Text.ToList([Phrase]),
b = List.Transform(a, each Text.Lower(_)),
c = List.Select(b, each List.Contains(low, _)),
d = Text.Combine(c, ""),
e = if d = Text.Reverse(d) then 1 else 0
][e]
),
Filter = Table.RemoveColumns(
Table.SelectRows(AddCheckPalind, each ([CheckPalind] = 1)),
"CheckPalind"
)
in
FilterPower Query solution 6 for List all Palindromic phrases, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.AddColumn(
Origen,
"Orig",
each Text.Lower(Text.Remove([Phrase], {",", "'", "?", "—", "!", ".", " "}))
),
b = Table.AddColumn(a, "Reversed", each Text.Reverse([Orig])),
Sol = Table.RenameColumns(
Table.SelectRows(b, each [Orig] = [Reversed])[[Phrase]],
{"Phrase", "Answer Expected"}
)
in
SolPower Query solution 7 for List all Palindromic phrases, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
PP = Table.SelectRows(
Source,
each
let
a = Text.Lower([Phrase]),
b = Text.ToList(a),
c = List.Select(b, each _ >= "a" and _ <= "z"),
d = List.Reverse(c)
in
c = d
)
in
PPPower Query solution 8 for List all Palindromic phrases, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
UppercaseAlphabet = List.Transform({"A" .. "Z"}, each Text.From(_, "en-US")),
TextToList = Table.AddColumn(Source, "TextToList", each Text.ToList(Text.Upper([Phrase]))),
FilterSigns = Table.AddColumn(
TextToList,
"FilterSigns",
each List.Select([TextToList], each List.Contains(UppercaseAlphabet, _))
),
TextCombine = Table.AddColumn(FilterSigns, "TextCombine", each Text.Combine([FilterSigns], "")),
Reverse = Table.AddColumn(TextCombine, "Reverse", each Text.Reverse([TextCombine])),
IfPalindrome = Table.AddColumn(
Reverse,
"IfPalindrome",
each if [TextCombine] = [Reverse] then 1 else 0
),
Res = Table.SelectRows(IfPalindrome, each ([IfPalindrome] = 1))
in
Res[Phrase]Solving the challenge of List all Palindromic phrases with Excel
Excel solution 1 for List all Palindromic phrases, proposed by Bo Rydobon 🇹🇭:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
99
),
b,
MID(
a,
s,
1
),
c,
XMATCH(
b,
CHAR(
SEQUENCE(
26,
,
65
)
)
),
AND(
TOCOL(
c,
3
)=TOCOL(
SORTBY(
c,
-s
),
3
)
)
)
)
)
)Excel solution 2 for List all Palindromic phrases, proposed by Bo Rydobon 🇹🇭:
=SORT(
CHAR(
SEQUENCE(
255
)
)
)Excel solution 3 for List all Palindromic phrases, proposed by Bo Rydobon 🇹🇭:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
99
),
b,
MID(
a,
s,
1
),
c,
SORTBY(
b,
-s
),
AND(
FILTER(
b,
b>"9"
)=FILTER(
c,
c>"9"
)
)
)
)
)
)Excel solution 4 for List all Palindromic phrases, proposed by Rick Rothstein:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(m,MID(UPPER(x),SEQUENCE(LEN(x)),1),t,CONCAT(IF(ABS(CODE(m)-77.5)<13,m,"")),c,LEN(t),AND(MID(t,SEQUENCE(c),1)=MID(t,SEQUENCE(c,,c,-1),1))))))Excel solution 5 for List all Palindromic phrases, proposed by John V.:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(r,ROW(1:64),b,CONCAT(TEXTSPLIT(UPPER(x),CHAR(VSTACK(r,r+90)))),CONCAT(MID(b,65-r,1))=b))))
With Bo Rydobon 🇹🇭 idea:
✅=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(s,ROW(1:98),c,MID(x,s,1),w,CONCAT(FILTER(c,c>"9")),w=CONCAT(MID(w,99-s,1))))))Excel solution 6 for List all Palindromic phrases, proposed by محمد حلمي:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(e,CONCAT(TEXTSPLIT(a,TEXTSPLIT(UPPER(a),CHAR(ROW(65:90)),,1))),e=CONCAT(MID(e,50-SEQUENCE(49),1))))))Excel solution 7 for List all Palindromic phrases, proposed by محمد حلمي:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
e,
CONCAT(
TEXTSPLIT(
a,
{"?",
"!",
",",
".",
"—",
"'",
" "}
)
),
e=CONCAT(
MID(
e,
50-SEQUENCE(
49
),
1
)
)
)
)
)
)
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
e,
CONCAT(
TEXTSPLIT(
a,
TEXTSPLIT(
UPPER(
a
),
CHAR(
ROW(
65:90
)
),
,
1
)
)
),
e=CONCAT(
MID(
e,
50-SEQUENCE(
49
),
1
)
)
)
)
)
)Excel solution 8 for List all Palindromic phrases, proposed by محمد حلمي:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
49
),
e,
CONCAT(
TEXTSPLIT(
a,
TEXTSPLIT(
UPPER(
a
),
CHAR(
s+64
),
,
1
)
)
),
e=CONCAT(
MID(
e,
50-s,
1
)
)
)
)
)
)Excel solution 9 for List all Palindromic phrases, proposed by محمد حلمي:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
m,
MID(
UPPER(
x
),
SEQUENCE(
LEN(
x
)
),
1
),
t,
CONCAT(
IF(
ABS(
CODE(
m
)-77.5
)<13,
m,
""
)
),
t=CONCAT(
MID(
t,
50-SEQUENCE(
49
),
1
)
)
)
)
)
)Excel solution 10 for List all Palindromic phrases, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
A2:A10,
func,
LAMBDA(
x,
REDUCE(
"",
SEQUENCE(
50
),
LAMBDA(
a,
n,
LET(
c,
MID(
d,
n,
1
),
t,
REPT(
c,
c>"9"
),
IF(
x,
a&t,
t&a
)
)
)
)
),
FILTER(
d,
func(
1
) =func(
0
)
)
)Excel solution 11 for List all Palindromic phrases, proposed by Kris Jaganah:
=TOCOL(
IFS(
MAP(
REDUCE(
A2:A10,
{",",
"'",
" ",
"?",
"—",
"!",
"."},
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
""
)
)
),
LAMBDA(
z,
LET(
a,
SEQUENCE(
LEN(
z
)
),
CONCAT(
SORTBY(
MID(
z,
a,
1
),
a,
-1
)
)
)=z
)
),
A2:A10
),
3
)Excel solution 12 for List all Palindromic phrases, proposed by Timothée BLIOT:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(z,
LET(A,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
B,
TOCOL(
MAP(
UPPER(
A
),
LAMBDA(
x,
IF(
ISNUMBER(
XMATCH(
x,
CHAR(
SEQUENCE(
26,
,
65
)
)
)
),
x,
1/0
)
)
),
3
),
SUM(--(MID(
CONCAT(
B
),
SEQUENCE(
ROWS(
B
),
,
ROWS(
B
),
-1
),
1
)=B))=ROWS(
B
)))))Excel solution 13 for List all Palindromic phrases, proposed by &Hussein SATOUR:
=FILTER(A2:A10, MAP(A2:A10, LAMBDA(x, LET(
a, IFERROR(CODE(MID(UPPER(x), SEQUENCE(99), 1)), 0),
b, IFERROR(CODE(MID(UPPER(x), SEQUENCE(99,,99,-1), 1)), 0),
PRODUCT(-(FILTER(a, (a>64)*(a<91)) = FILTER(b, (b>64)*(b<91)))))))<>0)Excel solution 14 for List all Palindromic phrases, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
_ph,
LOWER(
CONCAT(
TEXTSPLIT(
a,
,
TEXTSPLIT(
LOWER(
a
),
,
CHAR(
SEQUENCE(
26,
,
CODE(
"a"
)
)
),
1,
,
""
)
)
)
),
_s,
SEQUENCE(
LEN(
_ph
)
),
AND(
MID(
_ph,
_s,
1
) = MID(
_ph,
LEN(
_ph
) + 1 - _s,
1
)
)
)
)
)
)Excel solution 15 for List all Palindromic phrases, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(a,
LET(
_m,
MID(
LOWER(
a
),
SEQUENCE(
LEN(
a
)
),
1
),
_c,
CODE(
_m
),
_col1,
FILTER(_m,
(_c >= 97) * (_c <= 122)),
_col2,
SORTBY(
_col1,
-SEQUENCE(
ROWS(
_col1
)
)
),
AND(
_col1 = _col2
)
)
)
)
)Excel solution 16 for List all Palindromic phrases, proposed by Abdallah Ally:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(x,
LET(a,
LOWER(
x
),
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
MID(
a,
SEQUENCE(
LEN(
a
),
,
LEN(
a
),
-1
),
1
),
d,
LAMBDA(u,
(CODE(
u
)>96)*(CODE(
u
)<123)),
CONCAT(
IF(
d(
b
),
b,
""
)
)=CONCAT(
IF(
d(
c
),
c,
""
)
)))))Excel solution 17 for List all Palindromic phrases, proposed by 🇵🇪 Ned Navarrete C.:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
_r,
LET(
a,
MID(
LOWER(
_r
),
SEQUENCE(
LEN(
_r
)
);1
),
b,
SEQUENCE(
26
)+96,
c,
CONCAT(
FILTER(
a,
ISNUMBER(
MATCH(
CODE(
a
),
b,
0
)
)
)
),
c=CONCAT(
MID(
c,
SEQUENCE(
LEN(
c
),
,
LEN(
c
),
-1
),
1
)
)
)
)
)
)Excel solution 18 for List all Palindromic phrases, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A10,
del,
{",",
"'",
" ",
"?",
"—",
"!",
"."},
v,
MAP(
u,
LAMBDA(
x,
LET(
a,
CONCAT(
TEXTSPLIT(
x,
del
)
),
sq,
SEQUENCE(
LEN(
a
)
),
b,
CONCAT(
SORTBY(
MID(
a,
sq,
1
),
sq,
-1
)
),
a=b
)
)
),
FILTER(
u,
v
)
)Excel solution 19 for List all Palindromic phrases, proposed by Charles Roldan:
=LET(M,
LAMBDA(
g,
g(
g
)
),
_Trim,
M(LAMBDA(g,
LAMBDA(x,
IF(LEN(
x
),
LET(l,
LEFT(
x
),
REPT(
l,
ISNUMBER(
MATCH(
l,
CHAR(
SEQUENCE(
26,
,
CODE(
"a"
)
)
)
)
)
)
& g(
g
)(REPLACE(
x,
1,
1,
))),
)))),
_isPal,
M(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
)
)
)
),
B,
LAMBDA(
f,
LAMBDA(
g,
LAMBDA(
x,
f(
g(
x
)
)
)
)
),
(B(
Fl
)(Mp))(B(
_isPal
)(_Trim))
)(A2:A10)Excel solution 20 for List all Palindromic phrases, proposed by Pieter de Bruijn:
=LET(
a,
A2:A10,
FILTER(
a,
MAP(
a,
LAMBDA(
a,
LET(
m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
r,
FILTER(
m,
ISNUMBER(
XMATCH(
m,
CHAR(
ROW(
65:90
)
)
)
)
),
AND(
r=SORTBY(
r,
-SEQUENCE(
ROWS(
r
)
)
)
)
)
)
)
)
)
or just for fun not using lambda:
=LET(p,
A2:A10,
t,
MID(
p,
COLUMN(
A:AZ
),
1
),
r,
REPT(
t,
ISNUMBER(
XMATCH(
t,
CHAR(
ROW(
65:90
)
)
)
)
),
s,
SEQUENCE(
ROWS(
r
)
),
v,
TOCOL(
IFS(
LEN(
r
),
s
),
2
),
w,
SEQUENCE(
ROWS(
v
)
),
x,
TOCOL(
IFS(
LEN(
r
),
r
),
2
),
y,
SORTBY(
x,
v,
1,
w,
-1
),
z,
N(
x=y
),
m,
MMULT((N(
TOROW(
v
)=s
)),
v^0),
n,
MMULT((N(
TOROW(
v
)=s
)),
z),
FILTER(
p,
m=n
))Excel solution 21 for List all Palindromic phrases, proposed by Nicolas Micot:
=LET(_texteEppure;
MAP(MINUSCULE(
A2:A10
);
LAMBDA(l_texte;
LET(_lettres;
STXT(
l_texte;
SEQUENCE(
NBCAR(
l_texte
)
);
1
);
CONCAT(FILTRE(_lettres;
(CODE(
_lettres
)>=97)*(CODE(
_lettres
)<=122);
"")))));
FILTRE(
A2:A10;
MAP(
_texteEppure;
LAMBDA(
l_texte;
l_texte=CONCAT(
STXT(
l_texte;
SEQUENCE(
NBCAR(
l_texte
);
;
NBCAR(
l_texte
);
-1
);
1
)
)
)
)
))Excel solution 22 for List all Palindromic phrases, proposed by Ziad A.:
=FILTER(
A:A,
MAP(
A:A,
LAMBDA(
a,
LET(
b,
REGEXREPLACE(
a,
"[^A-Za-z]",
),
b=JOIN(
,
MID(
b,
99-ROW(
1:98
),
1
)
)
)
)
)
)Excel solution 23 for List all Palindromic phrases, proposed by Giorgi Goderdzishvili:
=LET(
fl,
MAP(A2:A10,
LAMBDA(x,
LET(
snt,
x,
chr,
LOWER(
MID(
snt,
SEQUENCE(
,
LEN(
snt
)
),
1
)
),
flt,
FILTER(chr,
NOT((CODE(
chr
)>96)*(CODE(
chr
)<123))),
sb,
REDUCE(
LOWER(
snt
),
flt,
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
""
)
)
),
ln,
LEN(
sb
),
chck,
SUM(--(MID(
sb,
SEQUENCE(
,
ln
),
1
)=MID(
sb,
SEQUENCE(
,
ln,
ln,
-1
),
1
)))=ln,
chck))),
FILTER(
A2:A10,
fl
))Excel solution 24 for List all Palindromic phrases, proposed by Abdelrahman Omer, MBA, PMP:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
b,
LOWER(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
c,
CHAR(
FILTER(
CODE(
b
),
ABS(
CODE(
b
)-123
)<27
)
),
d,
SORTBY(
c,
SEQUENCE(
COUNTA(
c
)
),
-1
),
CONCAT(
d
)=CONCAT(
c
)
)
)
)
)Excel solution 25 for List all Palindromic phrases, proposed by Anup Kumar:
=FILTER(A2:A10,BYROW(A2:A10,LAMBDA(z,LET(
phr,LOWER(z),
spt,MID(phr,SEQUENCE(,LEN(phr)),1),
alp,FILTER(spt,ISNUMBER(XMATCH(spt,UNICHAR(SEQUENCE(,26,97))))),
AND(alp=SORTBY(alp,SEQUENCE(,COUNTA(alp),0,-1)))
))))Excel solution 26 for List all Palindromic phrases, proposed by Rayan S.:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(a,
LET(
p, MID(LOWER(a), SEQUENCE(LEN(a)), 1),
x, FILTER(p, ((CODE(p) > 64) * (CODE(p) < 123))),
s, -SEQUENCE(COUNTA(x), , -COUNTA(x)),
SUM(--(INDEX(x, s) = x)) = COUNTA(x)
)
)
)
)Excel solution 27 for List all Palindromic phrases, proposed by Hazem Hassan:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(a,
CHAR(
SEQUENCE(
26,
,
65
)
),
b,
TEXTSPLIT(
x,
,
TEXTSPLIT(
x,
,
VSTACK(
a,
LOWER(
a
)
),
1
),
1
),
d,
CONCAT(
b
),
e,
LEN(
d
),
f,
SEQUENCE(
e
),
IF(SUM(1*(MID(
d,
f,
1
)=MID(
d,
SORT(
f,
,
-1
),
1
)))=e,
x,
1/0)))),
3)Excel solution 28 for List all Palindromic phrases, proposed by Jeff Blakley:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(c,T&OCOL(1/(1/DECIMAL(MID(x,SEQUENCE(LEN(x)),1),36)),2),s,SEQUENCE(ROWS(c)),SUM(-(SORTBY(c,s)<>SORTBY(c,-s)))=0))))Solving the challenge of List all Palindromic phrases with Python in Excel
Python in Excel solution 1 for List all Palindromic phrases, proposed by Bo Rydobon 🇹🇭:
import re
[a for a in xl("A2:A10")[0] if (b:=re.sub(r'W','',a).lower())==b[::-1]]
Fixed underscore
[a for a in xl("A2:A10")[0] if (b:=re.sub(r'[^A-Z]','',a.upper()))==b[::-1]]
Python in Excel solution 2 for List all Palindromic phrases, proposed by John V.:
Hi everyone!
def p(s):
c = re.sub(r'[^a-zA-Z]', '', s).lower()
return c == c[::-1]
[i for i in xl("A2:A10")[0] if p(i)]
p = lambda s: (c := re.sub(r'[^a-zA-Z]', '', s).lower()) == c[::-1]
list(filter(p, xl("A2:A10")[0]))
Blessings!
Solving the challenge of List all Palindromic phrases with R
R solution 1 for List all Palindromic phrases, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(stringi)
input = read_excel("Palindromic Phrase.xlsx", range = "A1:A10")
test = read_excel("Palindromic Phrase.xlsx", range = "B1:B6")
result = input %>%
mutate(phrase = Phrase %>%
str_remove_all("[[:punct:]]") %>%
str_remove_all(" ") %>%
str_to_lower(),
is_palindromic = phrase == stri_reverse(phrase)) %>%
filter(is_palindromic) %>%
select(`Answer Expected` = Phrase)
identical(test, result)
#> [1] TRUE
R solution 2 for List all Palindromic phrases, proposed by Krzysztof Nowak:
library(tidyverse)
CompareReversed <- function(x) {
x <- tolower(x)
Cleaned <- paste(unlist(str_extract_all(x,"[[:alpha:]]+")),collapse = "")
Reversed <- paste(rev(unlist(strsplit(Cleaned, ""))),collapse = "")
test <- Cleaned == Reversed
}
df |>
filter(map(Phrase,CompareReversed) == TRUE)
Answer
Solving the challenge of List all Palindromic phrases with Excel VBA
Excel VBA solution 1 for List all Palindromic phrases, proposed by Nicolas Micot:
=FILTRE(A2:A10;f_isPalindromic(A2:A10))
VBA function:
Function f_isPalindromic(Plage As Range) As Variant
Dim tableau As Variant, resultat As Variant
Dim texte As String, lettre As String
tableau = Plage.Value
ReDim resultat(1 To UBound(tableau, 1), 1 To 1)
For i = 1 To UBound(tableau, 1)
texte = ""
For x = 1 To Len(tableau(i, 1))
lettre = LCase(Mid(tableau(i, 1), x, 1))
Select Case lettre
Case "a" To "z"
texte = texte & lettre
End Select
Next x
resultat(i, 1) = texte = StrReverse(texte)
Next i
f_isPalindromic = resultat
End Function
&