List all Left Truncatable Primes. A Left Truncatable Prime is a prime number which contains no 0 and if the leading (“left”) digit is successively removed, then all resulting numbers are prime. For example, 9137, since 9137, 137, 37 and 7 are all primes.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 222
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Left Truncatable Primes with Power Query
Power Query solution 1 for List Left Truncatable Primes, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Filter = Table.SelectRows(
Source,
each
let
a = [Numbers],
l = List.Transform(
{1 .. Number.RoundDown(Number.Log10(a)) + 1},
each Number.Mod(a, Number.Power(10, _))
)
in
if List.Min(l) > 1 and List.IsDistinct(l) then
List.AllTrue(
List.Transform(
l,
(l) =>
List.AllTrue(
List.Transform(
{2 .. Number.RoundDown(Number.Sqrt(l))},
(s) => Number.Mod(l, s) > 0
)
)
)
)
else
false
)
in
FilterPower Query solution 2 for List Left Truncatable Primes, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Numbers],
IsPrime = (n) =>
not List.Accumulate(
{2 .. Number.RoundDown(Number.Sqrt(n))},
n = 1,
(s, d) => s or (Number.Mod(n, d) = 0)
),
S = List.Select(
Source,
each
let
t = Text.From(_)
in
not Text.Contains(t, "0")
and List.MatchesAll(
List.Transform({0 .. Text.Length(t) - 1}, each Number.From(Text.Range(t, _))),
IsPrime
)
)
in
SPower Query solution 3 for List Left Truncatable Primes, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Table.SelectRows(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
each not Text.Contains(Text.From([Numbers]), "0")
),
Sol = Table.SelectRows(
Table.AddColumn(
Source,
"Custom",
each List.Combine(
let
a = Text.ToList(Text.From([Numbers])),
b = List.Transform({1 .. List.Count(a)}, each Number.From(Text.Combine(List.LastN(a, _)))),
c = List.Transform(
{0 .. List.Count(b) - 1},
(x) =>
if b{x} >= 4 then
List.Distinct(
List.Transform(
{2 .. Number.RoundDown(Number.Sqrt(b{x}))},
each Number.Mod(b{x}, _) > 0
)
)
else if b{x} < 2 then
{false}
else
{true}
)
in
c
)
),
each List.AllTrue([Custom])
)[[Numbers]]
in
SolPower Query solution 4 for List Left Truncatable Primes, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LeftTruncatablePrimes = Csv.Document(Web.Contents("https://oeis.org/A024785/b024785.txt"), 2, "")[
Column2
],
#"Filtered Rows" = Table.SelectRows(
Source,
each List.Contains(LeftTruncatablePrimes, Text.From([Numbers]))
)
in
#"Filtered Rows"Power Query solution 5 for List Left Truncatable Primes, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LeftTruncPrimes = Table.TransformColumnTypes(
Table.SelectColumns(
Csv.Document(
Web.Contents("https://oeis.org/A024785/b024785.txt"),
2,
"",
ExtraValues.Ignore,
65001
),
{"Column2"}
),
{"Column2", Int64.Type}
),
AddIsLTP = Table.AddColumn(
Source,
"IsLTP",
each if List.Contains(LeftTruncPrimes[Column2], [Numbers]) then 1 else 0
),
RemoveErrors = Table.RemoveColumns(Table.RemoveRowsWithErrors(AddIsLTP, {"IsLTP"}), "IsLTP")
in
RemoveErrorsSolving the challenge of List Left Truncatable Primes with Excel
Excel solution 1 for List Left Truncatable Primes, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(A2:A10,LAMBDA(a,LET(s,SEQUENCE(a^0.5,,2),l,MOD(a,10^SEQUENCE(,LEN(a))),a/IF(ISERR(FIND(0,a))*AND(l>1),AND(IF(sExcel solution 2 for List Left Truncatable Primes, proposed by John V.:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,AND(ISERR(FIND(0,x)),MAP(--RIGHT(x,ROW(1:10)),LAMBDA(y,SUM(N(MOD(y,SEQUENCE(y^0.5))=0))+(y=1)))=1))))Excel solution 3 for List Left Truncatable Primes, proposed by محمد حلمي:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(v,LEN(a),
e,RIGHT(a,SEQUENCE(v)),IF(RIGHT(a)+0=1,,v=SUM(
MAP(e,LAMBDA(r,LET(i,r/SEQUENCE(SQRT(r)),
SUM(--(i=INT(i))))))))))))Excel solution 4 for List Left Truncatable Primes, proposed by محمد حلمي:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LEN(a)=SUM(MAP(RIGHT(a,SEQUENCE(LEN(a))),LAMBDA(r,LET(i,r/SEQUENCE(SQRT(r)),(r+0>1)*SUM(--(i=INT(i))))))))))Excel solution 5 for List Left Truncatable Primes, proposed by Kris Jaganah:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(a,LEN(x),b,MID(x,SEQUENCE(a),SEQUENCE(a,,a,-1)),MIN(IFS(b="1",0,b="2",1,b="3",1,LEFT(b)="0",0,1,(MOD((b-1)/6,1)=0)+((MOD((b+1)/6,1)=0))))))))Excel solution 6 for List Left Truncatable Primes, proposed by Julian Poeltl:
=LET(N,A2:A10,IP,LAMBDA(Number,LET(c,{2357},D,Number/TOCOL(VSTACK(c,SEQUENCE(Number^0,5/6,,2)*6+{-11}),3),OR(Number=c,AND(D-INT(D),Number>1)))),F,FILTER(N,NOT(ISNUMBER(SEARCH(0,N)))),FILTER(F,MAP(F,LAMBDA(A,SUM(--MAP(MID(A,SEQUENCE(LEN(A)),SEQUENCE(LEN(A),,LEN(A),1)),LAMBDA(A,IP(--A))))=LEN(A)))))Excel solution 7 for List Left Truncatable Primes, proposed by Timothée BLIOT:
=FILTER(A2:A10,MAP(A2:A10, LAMBDA(z,LET(D,LAMBDA(n,IF(n<5,2, VSTACK(2,SEQUENCE(ROUNDDOWN(((n^0.5)-3),0)/2+1,,3,2)))),P,LAMBDA(x,LET(A,D(x),SWITCH(x,1,0,2,1,--(SUM(MAP(A, LAMBDA(a,--(MOD(x,a)=0)))) =0)))),IF(ISNUMBER(FIND("0",z)),0,LET(E, MAP(SEQUENCE(LEN(z),,LEN(z),-1), LAMBDA(x, P(MID(z,LEN(z)-x+1,x)*1))), SUM(E)=ROWS(E) ))))))Excel solution 8 for List Left Truncatable Primes, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(_num,
LET(
_seq, SEQUENCE(LEN(_num), , LEN(_num), -1),
_left, RIGHT(_num, --_seq),
_sqrt, ROUNDUP(SQRT(_left), 0),
_cri, MAP(
_left,
_sqrt,
LAMBDA(x, y, BYROW(MOD(x, DROP(SEQUENCE(, y), , 1)), LAMBDA(a, NOT(OR(a = 0)))))
),
_fcri, BYCOL(_cri, LAMBDA(a, AND(a))),
IFERROR(_fcri, FALSE)
)
)
)
)Excel solution 9 for List Left Truncatable Primes, proposed by Abdallah Ally:
=FILTER(A2:A10,BYROW(A2:A10,LAMBDA(x, AND(ISERROR(FIND(0,VALUE(TOROW(RIGHT(x, SORT(SEQUENCE(LEN(x)),,-1)))))),OR(VALUE(TOROW(RIGHT(x, SORT(SEQUENCE(LEN(x)),,-1))))=2,AND(MOD(VALUE(TOROW(RIGHT(x, SORT(SEQUENCE(LEN(x)),,-1)))),ROW(INDIRECT("2:"&ROUNDUP(SQRT(VALUE(TOROW(RIGHT(x, SORT(SEQUENCE(LEN(x)),,-1))))),0))))<>0))))))Excel solution 10 for List Left Truncatable Primes, proposed by Charles Roldan:
=LET(A, LAMBDA(φ, LAMBDA(x, FILTER(x, φ(x)))),
B, LAMBDA(φ, LAMBDA(x, MAP(x, φ))),
π, B(LAMBDA(n, IF(n < 3, n > 1, AND(MOD(n, SEQUENCE(SQRT(n), , 2)))))),
τ, B(LAMBDA(n, AND(π(--RIGHT(n, SEQUENCE(LEN(n))))))),
A(τ)(A2:A10))Excel solution 11 for List Left Truncatable Primes, proposed by Ziad A.:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(P,LAMBDA(n,IF(--n=1,,COUNTIF(MOD(n,SEQUENCE(SQRT(n))),0)=1)),PRODUCT(--MAP(RIGHT(a,SEQUENCE(LEN(a))),LAMBDA(x,P(x))))))))Excel solution 12 for List Left Truncatable Primes, proposed by Daniel Garzia:
=FILTER(A2:A10,IFERROR(MAP(A2:A10,LAMBDA(n,LET(l,LEN(n),AND(MAP(--RIGHT(n,l-SEQUENCE(l,,0)),LAMBDA(x,AND(MOD(x,SEQUENCE(ROUNDUP(x^(1/2),)-1,,2))))))))),))Excel solution 13 for List Left Truncatable Primes, proposed by Quadri Olayinka Atharu:
=LET(_r,A2:A10,_s,DROP(IFNA(REDUCE("",_r,LAMBDA(a,y,VSTACK(a,--RIGHT(y,SEQUENCE(,LEN(y)))))),""),1),
_b,IFERROR(N(MAP(_s,LAMBDA(x,AND(x>1,MIN(MOD(x/SEQUENCE(INT(SQRT(x)),,2),1))>0)))),0),
_t1,BYROW(_b,LAMBDA(x,SUM(x)))=LEN(_r),
_t2,ISERR(FIND(0,_r)),
FILTER(_r,_t1*_t2))Excel solution 14 for List Left Truncatable Primes, proposed by Diarmuid Early:
=LET(isPrime,LAMBDA(rng,MAP(rng,
LAMBDA(n,IF(n<3,n=2,MIN(MOD(n,SEQUENCE(SQRT(n),,2)))>0)))),
input,A2:A10,
FILTER(input,MAP(input,LAMBDA(num,AND(isPrime(--RIGHT(num,SEQUENCE(,LEN(num)))))))))
isPrime is a custom function that returns true if an input number is prime and false otherwise. It's written with a MAP so it can take a range of arguments, but the main logic (the inner LAMBDA) is checking that n leaves a remainder when divided by every number from 2 to the square root of n.
Then we take the last 1, 2, 3... digits of the number, check if each is prime, wrap all that in an AND so we get a single TRUE if the number is a left truncatable prime and false otherwise, and finally filter the input on that.
Two MAPs and three LAMBDAs... someone will come up with something a lot more efficient than this, but I need to go to bed. : )
Edit to add: thanks to محمد حلمي for pointing out that my isPrime function had a bug for n=2, which I didn't notice because it didn't come up in this case. I tweaked it from IF(n>1,...) to IF(n<3,n=2,...)Excel solution 15 for List Left Truncatable Primes, proposed by Rayan S.:
=LET(f,MAP(A2:A10,LAMBDA(a,LET(
p,SUM(IF(RIGHT(a,1)+0={3,5,7},1,0)),
y,IFERROR(IF(FIND(0,a,1)>0,""),1),
IF(AND(p=1,y=1),a,0)))),FILTER(f,f>0))Excel solution 16 for List Left Truncatable Primes, proposed by Henriette Hamer:
=LET(input;$A$2:$A$10;FILTER(input;MAP(input;LAMBDA(b;SUM(MAP(LET(no_zero_input;SUBSTITUTE(b;"0";"");length;LEN(no_zero_input);MID(no_zero_input;SEQUENCE(;length);100));LAMBDA(a;LET(no_zero_input;a;divide;SEQUENCE(SQRT(a);;2);SUM(IF(RIGHT(no_zero_input;1)="1";1;IF(ROUND(no_zero_input/divide;0)=no_zero_input/divide;1;0)))))))))=0))
with a little help from Diarmuid Early, seeing him use sqrt(n) in a sequence, where initially I had n/2 which gave an error in the big numbers (apparently there is a limit to how long a sequence can be?)Excel solution 17 for List Left Truncatable Primes, proposed by Caroline Blake:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(_arr,LET(a,LEN(_arr),b,MID(_arr,SEQUENCE(a)+1,(a-SEQUENCE(a))),c,(MOD(SQRT(b),SEQUENCE(,SQRT(b)))),r,IF(ISERROR(FIND(0,_arr)),0,1),d,IFERROR(IF(c=0,1,0),0),SUM(--(d=1))+r)))=0)Solving the challenge of List Left Truncatable Primes with Python in Excel
Python in Excel solution 1 for List Left Truncatable Primes, proposed by Alejandro Campos:
def is_prime(n):
if n <= 1:
return False
if n <= 3:
return True
if n % 2 == 0 or n % 3 == 0:
return False
i = 5
while i * i <= n:
if n % i == 0 or n % (i + 2) == 0:
return False
i += 6
return True
def is_left_truncatable_prime(n):
if '0' in str(n):
return False
while n > 0:
if not is_prime(n):
return False
n = int(str(n)[1:]) if len(str(n)) > 1 else 0
return True
numbers = xl("A2:A10")[0]
left_truncatable_primes = [num for num in numbers if is_left_truncatable_prime(num)]
df = pd.DataFrame(left_truncatable_primes, columns=["Result in PY"])
df
&&&