List all the substrings from Number1 which are divisible by Number2. Result should not include any single 0 and should contain unique numbers and should be sorted. Ex. Number1 = 705, Number2 = 5, Substrings of 705 are 7, 0, 5, 70, 05, 705. Out of these, 5, 70, 05 and 705 are divisible by 5. Hence answer would be 5, 70, 705.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 281
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Divisible Substrings Sorted with Power Query
Power Query solution 1 for Find Divisible Substrings Sorted, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each
let
a = Text.From([Number1]),
l = Text.Length(a)
in
Text.Combine(
List.Sort(
List.Distinct(
List.Combine(
List.Transform(
{1 .. l},
(j) =>
List.Transform(
{0 .. l - j},
(i) =>
let
n = Number.From(Text.Middle(a, i, j))
in
if n > 0 and Number.Mod(n, [Number2]) = 0 then Text.From(n) else null
)
)
)
),
each Number.From(_)
),
", "
)
)
in
AnsPower Query solution 2 for Find Divisible Substrings Sorted, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each
let
n = Text.From([Number1])
in
Text.Combine(
List.Transform(
List.Select(
List.Sort(
List.Distinct(
List.Union(
List.Transform(
{1 .. Text.Length(n)},
each List.Transform(
{0 .. Text.Length(n) - _},
(p) => Number.From(Text.Middle(n, p, _))
)
)
)
)
),
(r) => Number.Mod(r, [Number2]) = Number.From(r = 0)
),
Text.From
),
", "
)
)
in
SPower Query solution 3 for Find Divisible Substrings Sorted, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
T = Text.From([Number1]),
L = Text.Length(T) - 1,
G = List.TransformMany(
{0 .. L},
(x) => {x .. L},
(x, y) =>
[
S = Text.Range(T, x, y - x + 1),
N = Number.From(S),
TF = Number.Mod(N, [Number2]) = 0 and N <> 0,
F = if TF then Text.From(N) else null
][F]
),
U = List.Distinct(List.RemoveNulls(G)),
S = List.Sort(U, (f) => Number.From(f)),
R = Text.Combine(S, ", ")
][R]
)
in
ReturnPower Query solution 4 for Find Divisible Substrings Sorted, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
(z) =>
let
a = Text.From(z[Number1]),
b = Text.Length(a),
c = List.Transform(
{1 .. b},
each List.Transform(
{0 .. b - 1},
(x) => {try Number.From(Text.Range(a, x, _)) otherwise null}
& {try Number.Mod(Number.From(Text.Range(a, x, _)), z[Number2]) otherwise null}
)
),
d = List.Combine(List.Transform(c, each List.Select(_, (y) => y{1} = 0))),
e = List.Sort(List.Select(List.Distinct(List.Transform(d, each _{0})), each _ <> 0)),
f = Text.Combine(List.Transform(e, Text.From), ", ")
in
f
)[[Answer]]
in
SolPower Query solution 5 for Find Divisible Substrings Sorted, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = Text.From([Number1]),
a1 = [Number2],
b = List.Transform(
{1 .. Text.Length(a)},
each List.Transform({0 .. Text.Length(a) - _}, (x) => Number.From(Text.Middle(a, x, _)))
),
c = List.Transform({b}, List.Combine){0},
d = List.Transform(c, each {_} & {Number.Mod(_, a1)}),
e = List.Select(d, each _{1} = 0 and _{0} <> 0),
f = Text.Combine(
List.Transform(List.Sort(List.Distinct(List.Transform(e, each _{0})), 0), Text.From),
", "
)
][f]
)
in
resPower Query solution 6 for Find Divisible Substrings Sorted, proposed by Szabolcs Phraner:
let
Source = Excel.CurrentWorkbook(){[Name = "Numbers"]}[Content],
ChangeType = Table.TransformColumnTypes(Source, {{"Number1", type text}, {"Number2", Int64.Type}}),
//Custom function to be used
FN_FilterNumbers = (number1 as text, number2 as number) =>
let
String = number1,
Length = Text.Length(String),
//Nested list for looping
Substring_Pos = List.Transform(List.Numbers(0, Length), each {_, {1 .. Length - _}}),
//Loop trough each offset position and collection of counts to create each possilbe substring
FilteredNumbers = List.Accumulate(
Substring_Pos,
{},
(s, c) =>
let
//Creates the substrings
SubstringList = List.Transform(c{1}, each Number.From(Text.Range(String, c{0}, _))),
//Combines and sorts the distinct list of substring numbers, filtering out zeros and items that are not divisible by number 2
Combine = List.Select(
List.Sort(List.Distinct(s & SubstringList)),
each _ / number2 = Number.Round(_ / number2, 0) and _ <> 0
)
in
Combine
)
in
// Combines the filtered list of numbers to a text string
Text.Combine(List.Transform(FilteredNumbers, Text.From), ", "),
Result = Table.AddColumn(
ChangeType,
"Result",
each FN_FilterNumbers([Number1], [Number2]),
type text
)
in
ResultSolving the challenge of Find Divisible Substrings Sorted with Excel
Excel solution 1 for Find Divisible Substrings Sorted, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A11,
B2:B11,
LAMBDA(a,
b,
LET(s,
SEQUENCE(
15
),
n,
--MID(
a,
s,
TOROW(
s
)
),
TEXTJOIN(", ",
,
SORT(UNIQUE(TOCOL(IF((MOD(
n,
b
)=0)*n,
n,
""),
3)))))))Excel solution 2 for Find Divisible Substrings Sorted, proposed by John V.:
=MAP(A2:A11,
B2:B11,
LAMBDA(x,
y,
LET(s,
ROW(
1:15
),
n,
--MID(
x,
s,
TOROW(
s
)
),
ARRAYTOTEXT(SORT(UNIQUE(TOCOL(n/(n>0)/(MOD(
n,
y
)=0),
2)))))))Excel solution 3 for Find Divisible Substrings Sorted, proposed by محمد حلمي:
=MAP(A2:A11,
B2:B11,
LAMBDA(a,
b,
LET(
s,
ROW(
1:20
),
i,
TOCOL(0&(MID(
a,
s,
TOROW(
s
)
)))+0,
TEXTJOIN(", ",
,
SORT(UNIQUE(IF(i*(MOD(
i,
b
)=0),
i,
"")))))))Excel solution 4 for Find Divisible Substrings Sorted, proposed by Kris Jaganah:
=MAP(A2:A11,
B2:B11,
LAMBDA(v,
w,
LET(a,
UNIQUE(
TOCOL(
--MID(
v,
SEQUENCE(
LEN(
v
)
),
SEQUENCE(
,
LEN(
v
)
)
)
)
),
ARRAYTOTEXT(SORT(FILTER(a,
(MOD(
a,
w
)=0)*(a<>0)))))))Excel solution 5 for Find Divisible Substrings Sorted, proposed by Timothée BLIOT:
=MAP(A2:A11,
B2:B11,
LAMBDA(r,
s,
LET(A,
--REDUCE(
r,
SEQUENCE(
LEN(
r
)- 1,
,
0
),
LAMBDA(
w,
v,
HSTACK(
w,
MID(
r,
SEQUENCE(
LEN(
r
)-v
),
v+1
)
)
)
),
B,
UNIQUE (TOCOL(
IF(
MOD(
A,
s
)=0,
A,
0
),
3
)),
ARRAYTOTEXT(
SORT(
FILTER(
B,
B<>0
)
)
))))Excel solution 6 for Find Divisible Substrings Sorted, proposed by Hussein SATOUR:
=MAP(A2:A11,
B2:B11,
LAMBDA(x,
y,
LET(a,
LEN(
x
),
b,
SORT(
UNIQUE(
TOCOL(
--MID(
x,
SEQUENCE(
a
),
SEQUENCE(
,
a
)
)
)
)
),
ARRAYTOTEXT(FILTER(b,
(b<>0)*(MOD(
b,
y
)=0))))))Excel solution 7 for Find Divisible Substrings Sorted, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A11,
B2:B11,
LAMBDA(a,
b,
LET(_s,
SEQUENCE(
LEN(
a
)
),
_n,
MID(
a,
TOROW(
_s
),
_s
),
ARRAYTOTEXT(UNIQUE(TOCOL(IF(MOD(
_n,
b
),
1/0,
(1/_n)^-1),
2))))))Excel solution 8 for Find Divisible Substrings Sorted, proposed by Duy Tùng:
=MAP(A2:A11,
B2:B11,
LAMBDA(x,
v,
LET(a,
UNIQUE(
TOCOL(
--MID(
x,
SEQUENCE(
,
LEN(
x
)
),
SEQUENCE(
LEN(
x
)
)
)
)
),
ARRAYTOTEXT(TOCOL(a/((a>0)*(MOD(
a,
v
)=0)),
3)))))Excel solution 9 for Find Divisible Substrings Sorted, proposed by Sunny Baggu:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
a,
b,
LET(
_num,
UNIQUE(
SORT(
TOCOL(
--MID(
a,
SEQUENCE(
LEN(
a
)
),
SEQUENCE(
,
LEN(
a
)
)
)
)
)
),
_numf,
FILTER(
_num,
_num > 0
),
ARRAYTOTEXT(
TOCOL(
IF(
MOD(
_numf,
b
) = 0,
_numf,
1 / 0
),
3
)
)
)
)
)Excel solution 10 for Find Divisible Substrings Sorted, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
m,
n,
LET(
s,
SEQUENCE(
LEN(
m
)
),
r,
--MID(
m,
s,
TOROW(
s
)
),
o,
SORT(
UNIQUE(
TOCOL(
IF(
r,
r,
NA()
),
3
)
)
),
ARRAYTOTEXT(
FILTER(
o,
MOD(
o,
n
)=0
)
)
)
)
)Excel solution 11 for Find Divisible Substrings Sorted, proposed by Abdallah Ally:
=MAP(A2:A11,
B2:B11,
LAMBDA(x,
y,
LET(a,
x,
b,
y,
c,
SEQUENCE(
LEN(
a
)
),
d,
SORT(
UNIQUE(
TOCOL(
--MID(
a,
c,
TOROW(
c
)
)
)
)
),
TEXTJOIN(", ",
TRUE,
FILTER(d,
(MOD(
d,
b
)=0)*d)))))Excel solution 12 for Find Divisible Substrings Sorted, proposed by 🇵🇪 Ned Navarrete C.:
=MAP( A2:A11,B2:B11,
LAMBDA(_n1,_n2, LET(
_rng,MID(_n1,SEQUENCE(LEN(_n1)),SEQUENCE(,LEN(_n1)))*1,
_sstr,UNIQUE(SORT(TOCOL(_rng*(_rng/_rng),2))),
TEXTJOIN(", ",1,MAP(_sstr, LAMBDA(_f, IF(MOD(_f,_n2)=0,_f,""))))
)
)
)Excel solution 13 for Find Divisible Substrings Sorted, proposed by Pieter de Bruijn:
=MAP(A2:A11,
B2:B11,
LAMBDA(x,
y,
LET(n,
SORT(
--UNIQUE(
TOCOL(
MID(
x,
ROW(
1:26
),
COLUMN(
A:Z
)
)
)
)
),
ARRAYTOTEXT(TOCOL(IFS(n,
n/(MOD(
n,
y
)=0)),
2)))))Excel solution 14 for Find Divisible Substrings Sorted, proposed by Ziad A.:
=MAP(A2:A11,
B2:B11,
LAMBDA(a,
b,
TEXTJOIN(", ",
1,
SORT(UNIQUE(LET(s,
SEQUENCE(
LEN(
a
)
),
REDUCE(,
s,
LAMBDA(_,
i,
LET(n,
--MID(
a,
i,
s
),
{_;IF(MOD(
n,
b
)+(n=0),
,
n)})))))))))Excel solution 15 for Find Divisible Substrings Sorted, proposed by Giorgi Goderdzishvili:
=MAP(A2:A11,
B2:B11,
LAMBDA(a,
b,
LET(
nm,
a,
sq,
SEQUENCE(
,
LEN(
nm
)
),
mp,
MAP(
sq,
LAMBDA(
x,
TEXTJOIN(
" ",
TRUE,
UNIQUE(
MID(
nm,
x,
SEQUENCE(
20
)
)
)
)&" "
)
),
fin,
UNIQUE(
--TEXTSPLIT(
CONCAT(
mp
),
" ",
,
TRUE
),
TRUE
),
TEXTJOIN(", ",
TRUE,
SORT(FILTER(fin,
(MOD(
fin,
b
)=0)*(fin<>0)),
,
,
TRUE)))))Excel solution 16 for Find Divisible Substrings Sorted, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
Number,
Divider,
LET(
Rcounter,
SEQUENCE(
LEN(
Number
)
),
Ccounter,
SEQUENCE(
,
LEN(
Number
)
),
GenNum,
MID(
Number,
Rcounter,
Ccounter
)*1,
SumNum,
SORT(
UNIQUE(
TOCOL(
GenNum
)
)
),
Remain,
MOD(
SumNum,
Divider
),
Filtered,
FILTER(
SumNum,
Remain=0
),
TEXTJOIN(
", ",
,
FILTER(
Filtered,
Filtered>0
)
)
)
)
)Excel solution 17 for Find Divisible Substrings Sorted, proposed by Anup Kumar:
=MAP(A2:A11,
B2:B11,
LAMBDA(num,
div,
LET(
lng,
LEN(
num
),
lst,
SORT(
UNIQUE(
TOCOL(
VALUE(
MID(
num,
SEQUENCE(
lng
),
SEQUENCE(
,
lng
)
)
)
)
)
),
ARRAYTOTEXT(FILTER(lst,
(MOD(
lst,
div
)=0)*(lst<>0))
))))Excel solution 18 for Find Divisible Substrings Sorted, proposed by samir tobeil:
=MAP(A2:A11,
B2:B11,
LAMBDA(x,
y,
LET(s,
SEQUENCE(
LEN(
x
)
),
r,
SORT(
UNIQUE(
TOCOL(
--MID(
x,
s,
TOROW(
s
)
)
)
)
),
TEXTJOIN(", ",
,
FILTER(r,
(MOD(
r,
y
)=0)*(r>0))))))Excel solution 19 for Find Divisible Substrings Sorted, proposed by Miguel Angel Franco García:
=LET(
a;
EXTRAE(
A2;
SECUENCIA(
;
LARGO(
A2
)
);
SECUENCIA(
LARGO(
A2
)
)
);
b;
ORDENAR(
UNICOS(
ABS(
ENCOL(
SI(
LARGO(
a
)=SECUENCIA(
LARGO(
A2
)
);
a;
NOD()
);
3
)
)
)
);
c;
FILTRAR(
b;
b<>0
);
d;
c/B2-ENTERO(
c/B2
);
TRANSPONER(
FILTRAR(
c;
d=0
)
)
)Excel solution 20 for Find Divisible Substrings Sorted, proposed by Kriddakorn Pongthanisorn:
=MAP(
A2:A11,
B2:B11,
LAMBDA(
_r1,
_r2,
LET(
_n,
_r1,
_d,
_r2,
_sub,
TOCOL(
BYROW(
SEQUENCE(
LEN(
_n
),
1
),
LAMBDA(
r,
ArrayFormula(
IF(
LEN(
MID(
_n,
SEQUENCE(
1,
LEN(
_n
)
),
r
)
)0
)
)
)
),
_output
)
)
) Solving the challenge of Find Divisible Substrings Sorted with Python
Python solution 1 for Find Divisible Substrings Sorted, proposed by Mungunbayar Bat-Ochir:
def main():
number1 = [43085, 233778, 2899424, 121471714, 593755574, 1901551899, 82039549206, 670622509652, 748754482070, 974595979650]
number2 = [5, 4, 12, 22, 7, 9, 18, 16, 72, 23]
nums = []
result = []
for i in range(len(number1)):
nums = get_substrings(number1[i])
result = get_divisible_nums(nums,number2[i])
print(result)
def get_substrings(num):
str_num = str(num)
length = len(str_num)
substrings = []
for i in range(length):
for j in range(i + 1, length + 1):
sub_num = int(str_num[i:j])
if sub_num != 0 and sub_num not in substrings:
substrings.append(sub_num)
substrings.sort()
return substrings
def get_divisible_nums(nums,divisor):
sub_nums = []
for num in nums:
if num % divisor == 0:
sub_nums.append(num)
return sub_nums
if __name__ == '__main__':
main()
Solving the challenge of Find Divisible Substrings Sorted with Python in Excel
Python in Excel solution 1 for Find Divisible Substrings Sorted, proposed by Bo Rydobon 🇹🇭:
[', '.join(map(str,np.unique([n for l in range(len(str(a))) for i in range(len(str(a))-l) if (n:=int(str(a)[i:i+l+1]))%b==0 and n>0] ))) for a,b in xl("A2:B11").values]Python in Excel solution 2 for Find Divisible Substrings Sorted, proposed by John V.:
Hi everyone!
One option [Python] could be:
def z(a, b):
a = str(a)
r = []
for y in range(len(a)):
for x in range(len(a) - y):
r.append(int(a[x : 1 + x + y]))
return ', '.join(map(str, sorted(pd.unique([i for i in r if i % b == 0 and i > 0]))))
xl("A1:B11", headers=True).apply(lambda x: z(x['Number1'], x['Number2']), axis=1).values
Blessings!
Python in Excel solution 3 for Find Divisible Substrings Sorted, proposed by Diarmuid Early:
[", ".join(map(str,sorted(pd.unique([outNum for y in range(len(str(num1))+1) for x in range(y) if (outNum:=int(str(num1)[x:y])) % num2 == 0 and outNum != 0])))) for num1, num2 in xl("A1:B11", headers=True).values]
I'm saving all my Python solutions to these challenges here if anyone wants to explore:
bit.ly/PythonLearningFolder
Solving the challenge of Find Divisible Substrings Sorted with R
R solution 1 for Find Divisible Substrings Sorted, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Divisible Substrings.xlsx")
all_substrings <- function(s) {
len <- str_length(s)
indices <- expand.grid(start=1:len, end=1:len) %>%
filter(start <= end)
map2_chr(indices$start, indices$end, ~str_sub(s, .x, .y))
}
is_divisible <- function(num, divisor) {
return(num %% divisor == 0)
}
result = input %>%
mutate(subs = map(.$Number1, all_substrings),
subs_num = map(subs, as.numeric),
subs_div = map2(subs_num, .$Number2, is_divisible),
divisible_subs = map2(subs_num, subs_div, ~ .x[.y]),
cleaned = map(divisible_subs, ~ sort(unique(.x[.x != 0]))),
my_answer = map_chr(cleaned, ~paste(.x, collapse = ", "))) %>%
select(1:3,9) %>%
mutate(test = `Answer Expected` == my_answer)
&&