Find the largest substring number which has alternating odd/even or even/odd digits. Ex. 39453 => Substrings with alternating odd/even or even odd/digits are: 94, 945, 45. Hence, largest substring number is 945.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 657
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Largest Alternating Digit Substring with Power Query
Power Query solution 1 for Largest Alternating Digit Substring, proposed by Luan Rodrigues:
let
Fonte = Table.AddColumn(
Tabela1,
"Result",
each
let
a = Text.ToList([Numbers]),
b = Table.FromRows(
List.Transform(
{0 .. List.Count(a) - 1},
(x) => {a{x}, Number.From(Number.IsOdd(Number.From(a{x})))}
)
),
c = Table.Group(b, "Column2", {"x", each _[Column1]}, 0),
d = Text.Combine(Table.AddColumn(c, "valid", each Text.Combine([x], "-"))[valid]),
e = Text.From(List.Max(List.Transform(Text.Split(d, "-"), (x) => Number.From(x))))
in
e
)
in
FontePower Query solution 2 for Largest Alternating Digit Substring, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
IsAlternate = (str) =>
List.AllTrue(
List.Transform(
{1 .. Text.Length(str) - 1},
each Number.Mod(Number.From(Text.At(str, _)), 2)
<> Number.Mod(Number.From(Text.At(str, _ - 1)), 2)
)
),
GetMaxSubstring = (str) =>
[
a = List.TransformMany(
List.TransformMany({str}, each {0 .. Text.Length(str) - 1}, (x, y) => {x, y}),
each {1 .. Text.Length(str)},
(x, y) => Text.Middle(x{0}, x{1}, y)
),
b = List.Select(a, each IsAlternate(_)),
c = Text.From(List.Max(List.Transform(b, each Number.From(_))))
][c],
AddCol = Table.AddColumn(Source, "My Answer", each GetMaxSubstring([Numbers])),
Transform = Table.TransformColumnTypes(AddCol, {"Answer Expected", type text}),
Result = Table.AddColumn(Transform, "Check", each [My Answer] = [Answer Expected])
in
ResultPower Query solution 3 for Largest Alternating Digit Substring, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Mod = (str, n) => Number.Mod(Number.From(Text.At(str, n)), 2),
IsAlternate = (str) =>
List.AllTrue(List.Transform({1 .. Text.Length(str) - 1}, each Mod(str, _) <> Mod(str, _ - 1))),
GetMaxSubstring = (str) =>
[
a = List.TransformMany({str}, each {0 .. Text.Length(str) - 1}, (x, y) => {x, y}),
b = List.TransformMany(a, each {1 .. Text.Length(str)}, (x, y) => Text.Middle(x{0}, x{1}, y)),
c = List.Select(b, each IsAlternate(_)),
d = Text.From(List.Max(List.Transform(c, each Number.From(_))))
][d],
AddCol = Table.AddColumn(Source, "My Answer", each GetMaxSubstring([Numbers])),
Transform = Table.TransformColumnTypes(AddCol, {"Answer Expected", type text}),
Result = Table.AddColumn(Transform, "Check", each [My Answer] = [Answer Expected])
in
ResultPower Query solution 4 for Largest Alternating Digit Substring, proposed by Peter Krkos:
let
F = (txt as text) =>
[
val = txt,
toList = List.Buffer(List.Transform(Text.ToList(val), Number.FromText)),
lt = List.Transform(List.Positions(toList), each List.Skip(toList, _)),
lg = List.Transform(
lt,
(lst) =>
List.Last(
List.Generate(
() => [x = 1, y = List.FirstN(lst, 2)],
each [x]
< List.Count(lst)
and List.Count(
List.Distinct(List.Transform(List.LastN([y], 2), (x) => Number.Mod(x, 2)))
)
> 1,
each [x = [x] + 1, y = [y] & {lst{x}}],
each Number.FromText(Text.Combine(List.Transform([y], Text.From)))
)
)
),
max = Number.ToText(List.Max(lg))
][max],
Ad_LargestSubstring = Table.AddColumn(Source, "Largest Substring", each F([Numbers]), type text)
in
Ad_LargestSubstringPower Query solution 5 for Largest Alternating Digit Substring, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
P = each Value.Metadata(_)[x],
L = each Number.From(_),
C = Table.AddColumn(
H,
"Answer",
each [
M = Table.FromColumns(
{List.Transform(Text.ToList([Numbers]), each Number.IsOdd(L(_)) meta [x = _])},
{"x"}
),
S = List.Sort(
Text.Split(
Text.Combine(
Table.Group(
M,
"x",
{
"y",
each if List.Count([x]) > 1 then P([x]{0}) & "-" & P(List.Last([x])) else P([x]{0})
},
0
)[y]
),
"-"
),
{L, 1}
){0}
][S]
)
in
CPower Query solution 6 for Largest Alternating Digit Substring, proposed by Tyler N.:
let
a = YourTable,
b = Table.AddColumn(
a,
"ans",
each
let
a = [Numbers],
b = Text.ToList(a),
c = List.Transform,
d = Number.From,
e = (f) => List.Sum(c({0, 1}, each Number.Power(- 1, d(b{f + _})))) = 0,
f = (g, h, i, j) =>
if h = 0 then
j & {i}
else
@f(g + 1, h - 1, {{b{g + 1}}, i & {b{g + 1}}}{d(e(g))}, {j & {i}, j}{d(e(g))}),
k = Text.From(List.Max(c(f(0, Text.Length(a) - 1, {b{0}}, {}), each d(Text.Combine(_)))))
in
k
)
in
bSolving the challenge of Largest Alternating Digit Substring with Excel
Excel solution 1 for Largest Alternating Digit Substring, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
s,
SEQUENCE(
LEN(
z
)
),
r,
REDUCE(
0,
s,
LAMBDA(
a,
x,
VSTACK(
a,
MID(
z,
s,
x
)
)
)
),
n,
SCAN(
"",
r,
LAMBDA(
a,
x,
CONCAT(
MOD(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
2
)
)
)
),
MAX(
0+FILTER(
r,
TEXTAFTER(
n&RIGHT(
n
),
{"11",
"00"},
,
,
,
""
)=""
)
)
)
)
)Excel solution 2 for Largest Alternating Digit Substring, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),b,IF(ISEVEN(-a),0,1),c,VSTACK(0,DROP(b,-1)),d,DROP(GROUPBY(SCAN(0,b=c,SUM),a,CONCAT,,0),,1),TAKE(SORTBY(d,-d),1))))Excel solution 3 for Largest Alternating Digit Substring, proposed by Julian Poeltl:
=MAP(A2:A10,
LAMBDA(A,
LET(N,
UNIQUE(
TOCOL(
MID(
A,
SEQUENCE(
LEN(
A
)
),
SEQUENCE(
,
LEN(
A
)
)
)
)
),
MAX(--FILTER(N,
1=IFERROR(MAP(N,
LAMBDA(A,
LET(S,
ISODD(
--MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
),
PRODUCT(--(DROP(
DROP(
S,
1
)<>S,
-1
)))))),
1))))))Excel solution 4 for Largest Alternating Digit Substring, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(x,TAKE(SORT(TOCOL(--REGEXEXTRACT(x,"([13579])?(([02468])([13579]))+([02468])?",1))),-1)))Excel solution 5 for Largest Alternating Digit Substring, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
z,
MAX(
--REDUCE(
{0;0},
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(
x,
y,
LET(
a,
TAKE(
x,
-1
),
VSTACK(
DROP(
x,
-1
),
IF(
ISEVEN(
y
)=ISEVEN(
a
),
VSTACK(
a,
y
),
a&y
)
)
)
)
)
)
)
)Excel solution 6 for Largest Alternating Digit Substring, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)
),
m,
MID(
a,
s,
1
),
t,
TEXTSPLIT(
CONCAT(
TOROW(
IF(
MOD(
m+s,
2
)={1,
0},
m,
" "
),
,
1
)
),
" "
),
@SORTBY(
t,
-t
)
)
)
)Excel solution 7 for Largest Alternating Digit Substring, proposed by Duy Tùng:
=MAP(
A2:A10,
LAMBDA(
v,
LET(
a,
--MID(
v,
SEQUENCE(
LEN(
v
)-1
),
SEQUENCE(
,
LEN(
v
),
2
)
),
MAX(
IF(
MAP(
a,
LAMBDA(
x,
LET(
a,
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
IFERROR(
FIND(
CONCAT(
N(
ISEVEN(
a
)
)
),
REPT(
10,
20
)
),
""
)
)
)
)<"",
a,
""
)
)
)
)
)Excel solution 8 for Largest Alternating Digit Substring, proposed by Anshu Bantra:
= str(num) # Convert number to string
max_substr = ""
current_substr = num_str[0]
for i in range(1, len(num_str)):
if (int(num_str[i-1]) % 2 == 0 and int(num_str[i]) % 2 != 0) or
(int(num_str[i-1]) % 2 != 0 and int(num_str[i]) % 2 == 0):
current_substr += num_str[i]
else:
max_substr = max(max_substr, current_substr, key=len)
current_substr = num_str[i]
return max(max_substr, current_substr, key=len)
df = to_df(REF("A1:A10"))
df['Answer'] = df['Numbers'].apply(longest_alternating_substring)Excel solution 9 for Largest Alternating Digit Substring, proposed by Md. Zohurul Islam:
=LET(
num,
A2:A10,
U,
LAMBDA(
w,
SORT(
UNIQUE(
TOCOL(
--MID(
w,
SEQUENCE(
LEN(
w
)-1
),
SEQUENCE(
,
LEN(
w
),
2
)
)
)
)
)
),
V,
LAMBDA(
z,
MAP(
z,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
MAP(
a,
LAMBDA(
p,
ABS(
ISEVEN(
p
)
)
)
),
c,
CONCAT(
b
),
d,
SEARCH(
c,
REPT(
10,
199
)
),
e,
IFERROR(
d,
0
),
e
)
)
)
),
w,
MAP(
num,
& LAMBDA(
f,
LET(
g,
U(
f
),
h,
V(
g
),
i,
MAX(
FILTER(
g,
h>0
)
),
i
)
)
),
w
)Solving the challenge of Largest Alternating Digit Substring with Python
Python solution 1 for Largest Alternating Digit Substring, proposed by Luan Rodrigues:
import pandas as pd
file = r"Excel_Challenge_657 - Largest Alternating Even Odd or Odd Even Substring.xlsx"
df = pd.read_excel(file,usecols='A')
def trasnform(x):
a = pd.DataFrame([[i, int(int(i) % 2 != 0)] for i in str(x)],columns=['Column1','Column2'])
a['group'] = (a['Column2'] != a['Column2'].shift()).cumsum()
a = ''.join(a.groupby('group')['Column1'].apply(lambda x: '-'.join(list(x)) ))
b = max([int(i) for i in a.split('-')])
return b
dfs = df['Numbers'].apply(trasnform).reset_index()
print(dfs)
Python solution 2 for Largest Alternating Digit Substring, proposed by Abdallah Ally:
import pandas as pd
from itertools import combinations
def is_alternate(text):
return all(int(text[i]) % 2 != int(text[i - 1]) % 2 for i in range(1, len(text)))
def get_max_substring(text):
str_nums = [
s for n in range(1, len(text) + 1)
for str_num in combinations(text, n)
if (s:=''.join(str_num)) in text
]
return max(int(s) for s in str_nums if is_alternate(s))
file_path = 'Excel_Challenge_657 - Largest Alternating Even Odd or Odd Even Substring.xlsx'
df = pd.read_excel(io=file_path)
# Perform data manipulation
df['My Answer'] = df['Numbers'].map(str).map(get_max_substring)
df['Check'] = df['My Answer'] == df['Answer Expected']
df
Solving the challenge of Largest Alternating Digit Substring with Python in Excel
Python in Excel solution 1 for Largest Alternating Digit Substring, proposed by Alejandro Campos:
def larg_alter_subs(number: str) -> str:
return str(max((int(number[i:j]) for i in range(len(number)) for j in range(
i+1, len(number)+1) if all(int(number[k]) % 2 != int(number[k-1]) % 2 for k in range(i+1, j))), default=0))
numbers = xl("A2:A10")[0]
df = pd.DataFrame([(num, larg_alter_subs(num)) for num in numbers], columns=['Number', 'Largest Alternating Substring'])
Solving the challenge of Largest Alternating Digit Substring with R
R solution 1 for Largest Alternating Digit Substring, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/657 Largest Alternating Even Odd or Odd Even Substring.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
generate_substrings <- function(num_str) {
nchar_num_str <- nchar(num_str)
unique(flatten_chr(map(seq_len(nchar_num_str), function(start) {
map_chr(start:nchar_num_str, function(end) substr(num_str, start, end))
})))
}
is_alternating <- function(num_str) {
all(diff(as.numeric(strsplit(num_str, "")[[1]]) %% 2) != 0)
}
largest_alternating_substring <- function(number) {
num_str <- as.character(number)
valid_substrings <- generate_substrings(num_str) %>%
map_chr(~ gsub("^0+", "", .x) %>% ifelse(. == "", "0", .)) %>%
keep(is_alternating)
if (length(valid_substrings) == 0) return(NA)
max(valid_substrings[nchar(valid_substrings) == max(nchar(valid_substrings))])
}
result = input %>%
mutate(LAS = map_chr(Numbers, largest_alternating_substring))
all.equal(result$LAS, test$`Answer Expected`, check.attributes = FALSE)
# [1] TRUE
&&