Palindromes are those words which are same even when read from backwards. For example, radar will be radar only even when read from backwards. List those Palindromes which are the longest in A2:A10.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 47
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Longest Palindromic Word with Power Query
Power Query solution 1 for Longest Palindromic Word, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Select = Table.SelectRows(Source, each Text.Lower(Text.Reverse([Words])) = Text.Lower([Words])),
Result = Table.SelectRows(
Select,
each Text.Length([Words]) = List.Max(List.Transform(Select[Words], Text.Length))
)
in
ResultPower Query solution 2 for Longest Palindromic Word, proposed by Brian Julius:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45W8k0sKlaK1YlWcs4sy0yGs3IyE/PAnHCYvEtqZlpmagqY7ZdfCpH1TUxJzAWzgvJLEkvyi8Bsp9KcpPxSoLZYAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Words = _t]
),
Reverse = Table.AddColumn(
Source,
"Backwards",
each if Text.Lower([Words]) = Text.Lower(Text.Reverse([Words])) then [Words] else null
),
Filter = Table.SelectRows(Reverse, each ([Backwards] <> null)),
LengthCol = Table.TransformColumns(Filter, {{"Backwards", Text.Length, Int64.Type}}),
MaxLen = List.Max(LengthCol[Backwards]),
Select = Table.RemoveColumns(Table.SelectRows(LengthCol, each [Backwards] = MaxLen), "Backwards")
in
SelectPower Query solution 3 for Longest Palindromic Word, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Criteria = Table.AddColumn(Source, "Custom", each Number.From(Text.Lower(Text.Reverse([Words]))=
Text.Lower([Words]))*Text.Length([Words])),
Filtered = Table.SelectRows(Criteria, each ([Custom] = List.Max(Criteria[Custom]))),
Final = Table.SelectColumns(Filtered,{"Words"})
in
Final
My Formula approach was similar to Aditya Kumar Darak and Jardiel Euflázio, so tried PQ.
Power Query solution 4 for Longest Palindromic Word, proposed by Antriksh Sharma:
let
Source = DataSource,
ChangedType = Table.TransformColumnTypes(Source, {{"Words", type text}}),
AddedCustom = Table.AddColumn(
ChangedType,
"WordLength",
each
if Comparer.Equals(Comparer.OrdinalIgnoreCase, [Words], Text.Reverse([Words])) then
Text.Length([Words])
else
null
),
MaxLength = List.Max(AddedCustom[WordLength]),
Result = Table.SelectRows(AddedCustom, each [WordLength] = MaxLength)[[Words]]
in
ResultPower Query solution 5 for Longest Palindromic Word, proposed by Venkata Rajesh:
let
Source = Data[Words],
Palindromes = List.Select(Source, each Text.Lower(_) = Text.Reverse(Text.Lower(_))),
Result = List.Select(
Palindromes,
each Text.Length(_) = List.Max(List.Transform(Palindromes, each Text.Length(_)), 1)
)
in
ResultSolving the challenge of Longest Palindromic Word with Excel
Excel solution 1 for Longest Palindromic Word, proposed by John V.:
=LET(
w,
A2:A10,
c,
LEN(
w
),
z,
w=MAP(
w,
LAMBDA(
x,
LET(
n,
LEN(
x
),
CONCAT(
MID(
x,
1+n-SEQUENCE(
n
),
1
)
)
)
)
),
FILTER(
w,
z*c=MAX(
z*c
)
)
)
=LET(w,
A2:A10,
c,
MAP(w,
LEN(
w
),
LAMBDA(x,
n,
n*(CONCAT(
MID(
x,
1+n-SEQUENCE(
n
),
1
)
)=x))),
FILTER(
w,
c=MAX(
c
)
))Excel solution 2 for Longest Palindromic Word, proposed by Julian Poeltl:
=LET(
W,
A2:A10,
L,
LEN(
W
),
R,
ROUNDDOWN(
L/2,
0
),
P,
MAP(
W,
R,
L,
LAMBDA(
A,
B,
C,
LEFT(
A,
B
)=CONCAT(
MID(
A,
SEQUENCE(
B,
,
C,
-1
),
1
)
)
)
),
F,
FILTER(
W,
P
),
LL,
LEN(
F
),
FILTER(
F,
LL=MAX(
LL
)
)
)Excel solution 3 for Longest Palindromic Word, proposed by Aditya Kumar Darak 🇮🇳:
= LET(
_calc,
MAP(
A2:A10,
LEN(
A2:A10
),
LAMBDA(
a,
b,
(CONCAT(
MID(
a,
SEQUENCE(
b,
,
b,
-1
),
1
)
) = a) * b)),
FILTER(
A2:A10,
_calc = MAX(
_calc
)
))Excel solution 4 for Longest Palindromic Word, proposed by Timothée BLIOT:
=LET(
Words,
A2:A10,
ReversedWords,
BYROW(
Words,
LAMBDA(
r,
PROPER(
CONCAT(
SORTBY(
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
),
SEQUENCE(
LEN(
r
)
),
-1
)
)
)
)
),
IsPalindrome,
IF(
Words=ReversedWords,
1,
0
),
FILTER(
Words,
IsPalindrome,
""
)
)Excel solution 5 for Longest Palindromic Word, proposed by Hussein SATOUR:
=LET(
a,
A2:A10,
FILTER(
a,
a = BYROW(
a,
LAMBDA(
x,
CONCAT(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
)
)
)
)
)Excel solution 6 for Longest Palindromic Word, proposed by Charles Roldan:
=LET(isMax,
LAMBDA(
x,
x = MAX(
x
)
),
isPal,
LAMBDA(
g,
g(
g
)
)(LAMBDA(g,
LAMBDA(x,
IF(LEFT(
x
) = RIGHT(
x
),
IFERROR(g(
g
)(MID(
x,
2,
LEN(
x
) - 2
)),
TRUE))))),
LAMBDA(
x,
FILTER(
x,
isMax(
LEN(
x
) * MAP(
x,
isPal
)
)
)
))(A2:A10)Excel solution 7 for Longest Palindromic Word, proposed by Jardiel Euflázio:
=LET(a,
A2:A10,
c,
LEN(
a
),
(FILTER(
a,
LET(
b,
BYROW(
a,
LAMBDA(
a,
LET(
c,
LEN(
a
),
CONCAT(
MID(
a,
SEQUENCE(
c,
,
c,
-1
),
1
)
)=a
)
)
),
b*c=MAX(
b*c
)
)
)))Excel solution 8 for Longest Palindromic Word, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(
r,
A2:A10,
p,
FILTER(
r,
MAP(
r,
LAMBDA(
x,
x=CONCAT(
MID(
x,
LEN(
x
)-SEQUENCE(
,
L
EN(
x
)
)+1,
1
)
)
)
)
),
FILTER(
p,
LEN(
p
)=MAX(
LEN(
p
)
)
)
)Excel solution 9 for Longest Palindromic Word, proposed by Sarun Chimamphant:
=LET(
a,
A2:A10,
b,
LEN(
a
),
c,
FILTER(
a,
MAP(
a,
b,
LAMBDA(
_a,
_b,
AND(
MID(
_a,
SEQUENCE(
_b/2
),
1
)=MID(
_a,
SEQUENCE(
_b/2,
,
_b,
-1
),
1
)
)
)
)
),
FILTER(
c,
LEN(
c
)=MAX(
LEN(
c
)
)
)
)Excel solution 10 for Longest Palindromic Word, proposed by Fábio Gatti:
=LAMBDA(
Words,
LET(
fxReverse,
LAMBDA(
Text,
LET(
vLen,
LEN(
Text
),
vSeq,
SEQUENCE(
vLen,
,
vLen,
-1
),
CONCAT(
MID(
Text,
vSeq,
1
)
)
)
),
vWord,
BYROW(
Words,
fxReverse
),
vCompare,
Words=vWord,
vFilter,
FILTER(
Words,
vCompare
),
vLen,
LEN(
vFilter
),
vResult,
FILTER(
vFilter,
vLen=MAX(
vLen
)
),
vResult
)
)(A2:A10)Excel solution 11 for Longest Palindromic Word, proposed by Wouter Magre:
=LET(
p,
FILTER(
Words,
BYROW(
Words,
LAMBDA(
w,
LET(
l,
LEN(
w
),
r,
CONCAT(
MID(
w,
SEQUENCE(
1,
l,
l,
-1
),
1
)
),
r=w
)
)
)
),
m,
MAX(
BYROW(
p,
LAMBDA(
w,
LEN(
w
)
)
)
),
FILTER(
p,
BYROW(
p,
LAMBDA(
w,
LEN(
w
)=m
)
)
)
)Solving the challenge of Longest Palindromic Word with Python in Excel
Python in Excel solution 1 for Longest Palindromic Word, proposed by Alejandro Campos:
def is_palindrome(word):
return word.lower() == word.lower()[::-1]
df = pd.DataFrame(longest_palindromes, columns=["Answer"])
df
&&&