Palindrome number is that which is same even when read backwards. Find the nearest Palindrome numbers for the numbers given in column A. Ex. 434…This has 2 nearest Palindrome numbers – 444, 424 Note – Some results were wrong in the picture posted. Excel file has been corrected while Linkedin doesn’t allow correcting the picture.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 343
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find nearest Palindrome numbers with Power Query
Power Query solution 1 for Find nearest Palindrome numbers, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
F = Text.From,
IsPal = each _ = Text.Reverse(_),
N = (_, c) => List.Select(if _ - c > 0 then {F(_ + c), F(_ - c)} else {F(_ + c)}, IsPal),
S = Table.TransformRows(
Source,
each Text.Combine(
N(
[Number],
List.Last(
List.Generate(() => 1, (i) => List.IsEmpty(N([Number], i)), (i) => i + 1, (i) => i + 1)
)
),
", "
)
)
in
SPower Query solution 2 for Find nearest Palindrome numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer Expected",
(s) =>
let
a = Record.ToList(
List.Last(
List.Generate(
() => [x = 1, y = false, z = false, w = 0, k = 0],
each [y] = false and [z] = false,
each [
x = [x] + 1,
y = s[Number] + [x] = Number.From(Text.Reverse(Text.From(s[Number] + [x]))),
z = s[Number] - [x] = Number.From(Text.Reverse(Text.From(s[Number] - [x]))),
w = s[Number] + [x] + 1,
k = s[Number] - [x] - 1
],
each [[w], [k]]
)
)
),
b = List.Select(List.Transform(a, Text.From), each _ = Text.Reverse(Text.From(_)))
in
Text.Combine(b, ", ")
)
in
SolSolving the challenge of Find nearest Palindrome numbers with Excel
Excel solution 1 for Find nearest Palindrome numbers, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,
LAMBDA(a,
LET(l,
LEN(
a
),
o,
ISODD(
l
),
b,
(l+1)/2,
m,
MAP(
LEFT(
a,
b
)+{1,
0,
-1},
LAMBDA(
c,
CONCAT(
c,
MID(
c,
b+1-o-SEQUENCE(
b-o
),
1
)
)
)
),
d,
ABS(
a-m
),
ARRAYTOTEXT(
FILTER(
m,
d=SMALL(
d,
SUM(
N(
d=0
)
)+1
)
)
))))Excel solution 2 for Find nearest Palindrome numbers, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
l,
LEN(
x
),
a,
-INT(
-l/2
),
m,
MAP(
LEFT(
x,
a
)+{1,
0,
-1},
LAMBDA(
v,
CONCAT(
v,
MID(
v,
a+ISEVEN(
l
)-SEQUENCE(
l-a
),
1
)
)
)
),
d,
ABS(
x-m
),
ARRAYTOTEXT(
FILTER(
m,
MIN(
IF(
d,
d
)
)=d
)
)
)
)
)Excel solution 3 for Find nearest Palindrome numbers, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
n,
LET(
s,
SEQUENCE(
11000
),
e,
LAMBDA(
c,
XLOOKUP(
1,
--MAP(
c,
LAMBDA(
a,
a=--CONCAT(
MID(
a,
21-SEQUENCE(
20
),
1
)
)
)
),
c
)
),
d,
IFERROR(
e(
n+s
),
),
m,
IFERROR(
e(
n-s
),
),
i,
n-m,
x,
d-n,
""&IFS(
x=i,
d&", "&m,
x>i,
m,
1,
d
)
)
)
)Excel solution 4 for Find nearest Palindrome numbers, proposed by محمد حلمي:
=LET(
v,
A2:A10,
d,
LAMBDA(
x,
LET(
r,
LAMBDA(
r,
a,
IF(
a=
--CONCAT(
MID(
a,
21-SEQUENCE(
20
),
1
)
),
a,
r(
r,
a+x
)
)
),
IFERROR(
MAP(
v,
LAMBDA(
a,
r(
r,
a+x
)
)
),
)
)
),
m,
d(
1
),
k,
d(
-1
),
i,
v-k,
x,
m-v,
""&IFS(
x=i,
m&", "&k,
x>i,
k,
1,
m
)
)Excel solution 5 for Find nearest Palindrome numbers, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
LEN(
x
),
b,
ROUNDUP(
a/2,
0
),
c,
SEQUENCE(
b
),
d,
MID(
x,
c,
1
),
e,
IF(
ISODD(
a
),
DROP(
d,
-1
),
d
),
f,
IFS(
TAKE(
--e,
-1
)=0,
VSTACK(
DROP(
e,
-1
),
1
),
TAKE(
--e,
-1
)=9,
VSTACK(
DROP(
e,
-2
),
DROP(
TAKE(
e,
-2
),
-1
)+1,
0
),
1,
e
),
g,
SORTBY(
f,
-TAKE(
c,
IF(
ISODD(
a
),
b-1,
b
)
)
),
h,
--CONCAT(f,
IF((c=b)*(ISODD(
a
)),
d,
""),
g),
i,
MID(
h,
b,
1
)+{1,
-1},
IFS((x=h)*ISEVEN(
a
),
TEXTJOIN(
", ",
,
REPLACE(
h,
b,
2,
i&i
)
),
(h=x)*ISODD(
a
),
TEXTJOIN(
", ",
,
REPLACE(
h,
b,
1,
i
)
),
1,
h))))Excel solution 6 for Find nearest Palindrome numbers, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
N,
LET(
LN,
LEN(
N
),
IP,
LEFT(
N,
LN/2
)=CONCAT(
MID(
N,
SEQUENCE(
LN/2,
,
LN,
-1
),
1
)
),
L,
LEFT(
N,
LN/2
),
LL,
LEN(
L
),
M,
IF(
ISODD(
LN
),
MID(
N,
LN/2+1,
1
),
""
),
R,
TEXTJOIN(
", ",
,
IF(
IP,
IF(
M<>"",
L&M+{1,
-1}&CONCAT(
MID(
L,
SEQUENCE(
LL,
,
LL,
-1
),
1
)
),
MAP(
L+{1,
-1},
LAMBDA(
A,
A&CONCAT(
MID(
A,
SEQUENCE(
LL,
,
LL,
-1
),
1
)
)
)
)
),
MAP(
L+{1,
-1},
LAMBDA(
A,
A&CONCAT(
MID(
A,
SEQUENCE(
LL,
,
LL,
-1
),
1
)
)
)
)
),
L&M&CONCAT(
MID(
L,
SEQUENCE(
LL,
,
LL,
-1
),
1
)
)
),
TS,
TEXTSPLIT(
R,
", "
),
A,
ABS(
N-TS
),
F,
FILTER(
TS,
A>0
),
DD,
ABS(
N-F
),
TEXTJOIN(
", ",
,
FILTER(
F,
DD=MIN(
DD
)
)
)
)
)
)Excel solution 7 for Find nearest Palindrome numbers, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
A,
LEN(
z
),
B,
--LEFT(
z,
CEILING(
A/2,
1
)
),
C,
ISODD(
A
),
D,
B-50+ROW(
1:100
),
E,
IF(
C,
LEFT(
D,
LEN(
D
)-1
),
D
),
F,
IFERROR(
E& IF(
C,
RIGHT(
D
),
""
)&MAP(
E,
LAMBDA(
x,
CONCAT(
MID(
x,
LEN(
x
)+1-SEQUENCE(
LEN(
x
)
),
1
)
)
)
),
0
),
G,
IFERROR(
ABS(
F-z
),
0
),
ARRAYTOTEXT(
FILTER(
F,
G=MIN(
FILTER(
G,
G<>0
)
)
)
)
)
)
)Excel solution 8 for Find nearest Palindrome numbers, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(t,
LET(
_l,
LEN(
t
),
_cri,
ISEVEN(
_l
),
_left,
LEFT(
t,
_l / 2
),
_m,
MID(
t,
1 + _l / 2,
1
),
_rev,
CONCAT(
MID(
_left,
LEN(
_left
) + 1 - SEQUENCE(
LEN(
_left
)
),
1
)
),
_list,
IF(
_cri = FALSE,
_left & _m + {-1; 0; 1} & _rev,
LET(
_a,
_left + {-1; 0; 1},
_a & MAP(
_a,
LAMBDA(
x,
CONCAT(
MID(
x,
LEN(
x
) + 1 - SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
)
),
_diff,
(_list - t),
_min,
MAX(
FILTER(
_diff,
_diff < 0
)
),
_max,
MIN(
FILTER(
_diff,
_diff > 0
)
),
_num1,
XLOOKUP(
_min,
_diff,
_list
),
_num2,
XLOOKUP(
_max,
_diff,
_list
),
IF(
ABS(
_min
) = ABS(
_max
),
TEXTJOIN(
",",
,
_num1,
_num2
),
IF(
ABS(
_min
) < ABS(
_max
),
_num1,
_num2
)
)
)
)
)Solving the challenge of Find nearest Palindrome numbers with R
_x000D_
R solution 1 for Find nearest Palindrome numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(stringi)
input = read_excel("Nearest Palindrome.xlsx", range = "A1:A10") %>% janitor::clean_names()
test = read_excel("Nearest Palindrome.xlsx", range = "B1:B10") %>% janitor::clean_names()
find_closest_palindromes <- function(number) {
num_str <- as.character(number)
len <- nchar(num_str)
is_odd <- len %% 2 == 1
half_len <- (len + 1) %/% 2
first_half <- substr(num_str, 1, half_len)
candidates <- map_dbl(c(1, 0, -1), ~ {
modified_first_half <- as.numeric(first_half) + .x
modified_first_half_str <- as.character(modified_first_half)
if (nchar(modified_first_half_str) < half_len) {
modified_first_half_str <- str_pad(modified_first_half_str, half_len, pad = "0")
}
second_half <- substr(modified_first_half_str, 1, half_len - is_odd)
as.numeric(paste0(modified_first_half_str, stri_reverse(second_half)))
})
differences <- abs(number - candidates)
valid_differences <- differences[differences != 0]
closest_values <- candidates[differences == min(valid_differences)]
return(paste(closest_values, collapse = ", "))
}
result = input %>%
mutate(result = map(number, find_closest_palindromes) %>% unlist())
_x000D_
&&