Convert ISBN10 to ISBN13 Numbers 1. Prefix 978 to ISBN10 2. Take the first 12 digits of step 1 3. Multiply first digit with 1, second with 3, third with 1, 4th with 3 and so on (basically alternate digits with 1 and 3) 4. Sum step 3, modulo by 10 of this sum and subtract the result from 10. This is the final digit which must be suffixed to step1. Ex. 0143452126 – Prefix 978 and take first 12 digits = 978014345212. Multiply alternate digits with 1 and 3. 9*1+7*3+8*1+0*3+1*1+4*3+3*1+4*3+5*1+2*3+1*1+2*3 = 84 => 10 – MODULE 10 of 84 = 10-4 = 6 Hence answer = 9780143452126
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 309
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Convert ISBN10 to ISBN13 Numbers with Power Query
Power Query solution 1 for Convert ISBN10 to ISBN13 Numbers, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
T = Text.From,
L = Text.Length,
C = (t) =>
if t = "" then
0
else
Number.From(Text.At(t, 0))
* (if Number.IsEven(L(t)) then 1 else 3) + @C(Text.End(t, L(t) - 1)),
S = Table.TransformRows(
Source,
each
let
i = "978" & Text.Start(T([ISBN10]), 9)
in
i & T(10 - Number.Mod(C(i), 10))
)
in
SPower Query solution 2 for Convert ISBN10 to ISBN13 Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"ISBN13",
each [
P = "978" & [ISBN10],
T = List.TransformMany(
{0 .. 11},
(x) => {{1, 3}{Number.Mod(x, 2)}},
(x, y) => Number.From(Text.At(P, x)) * y
),
L = 10 - Number.Mod(List.Sum(T), 10),
R = Text.Start(P, 12) & Text.From(L)
][R]
)
in
ReturnPower Query solution 3 for Convert ISBN10 to ISBN13 Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"ISBN13",
each
let
a = "978" & Text.From([ISBN10]),
b = Text.Start(a, 12),
c = List.Transform(Text.ToList(b), Number.From),
d = List.Sum(
List.Transform(List.Alternate(c, 1, 1, 0), each _ * 3) & List.Alternate(c, 1, 1, 1)
),
e = 10 - Number.Mod(d, 10),
f = b & Text.From(e)
in
f
)[[ISBN13]]
in
SolPower Query solution 4 for Convert ISBN10 to ISBN13 Numbers, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = Text.Start("978" & Text.From([ISBN10]), 12),
b = List.Combine(
List.Transform(
List.Split(Text.ToList(a), 2),
(x) => List.Combine(List.Zip({x, {"*1+", "*3+"}}))
)
),
c = Expression.Evaluate(Text.TrimEnd(Text.Combine(b), "+")),
d = a & Text.From(10 - Number.Mod(c, 10))
][d]
)
in
resPower Query solution 5 for Convert ISBN10 to ISBN13 Numbers, proposed by Challa Sai Kumar Reddy:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"ISBN13",
each
let
a = "978" & Text.From([ISBN10]),
b = Text.Start(a, 12),
c = List.Sum(List.Transform(Text.ToList(b), each Number.From(_))),
d = 10 - Number.Mod(c, 10),
e = b & Text.From(d)
in
e
)[[ISBN13]]
in
SolSolving the challenge of Convert ISBN10 to ISBN13 Numbers with Excel
Excel solution 1 for Convert ISBN10 to ISBN13 Numbers, proposed by Bo Rydobon 🇹🇭:
=MAP(
978&LEFT(
A2:A10,
9
),
LAMBDA(
n,
n&MOD(
-SUM(
MID(
n,
SEQUENCE(
6,
2
),
1
)*{1,
3}
),
10
)
)
)Excel solution 2 for Convert ISBN10 to ISBN13 Numbers, proposed by Rick Rothstein:
=MAP(A2:A10,LAMBDA(a,LET(n,LEFT(978&a,12),n&10-RIGHT(SUM(MID(n,SEQUENCE(,12),1)*{1,3,1,3,1,3,1,3,1,3,1,3})))))
Stealing an idea from Ziad A., this shortens to...
=MAP(A2:A10,LAMBDA(a,LET(n,LEFT(978&a,12),n&10-RIGHT(SUM(MID(n,SEQUENCE(6,2),1)*{1,3})))))Excel solution 3 for Convert ISBN10 to ISBN13 Numbers, proposed by John V.:
=LET(
s,
ROW(
1:12
),
b,
978&LEFT(
A2:A10,
9
),
b&10-RIGHT(
MMULT(
-MID(
b,
TOROW(
s
),
1
),
-1^s+2
)
)
)Excel solution 4 for Convert ISBN10 to ISBN13 Numbers, proposed by محمد حلمي:
=MAP(
978&A2:A10,
LAMBDA(
a,
LET(
x,
SEQUENCE(
12
),
LEFT(
a,
12
)&10-MOD(
SUM(
MID(
a,
x,
1
)*IF(
MOD(
x,
2
),
1,
3
)
),
10
)
)
)
)Excel solution 5 for Convert ISBN10 to ISBN13 Numbers, proposed by Kris Jaganah:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
LEFT(
978&x,
12
),
a&10-MOD(
SUM(
MMULT(
WRAPROWS(
--MID(
a,
SEQUENCE(
12
),
1
),
2
),
{1;3}
)
),
10
)
)
)
)Excel solution 6 for Convert ISBN10 to ISBN13 Numbers, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
A,
978&MID(
z,
1,
9
),
B,
LAMBDA(
a,
b,
MID(
REPT(
a,
b
),
SEQUENCE(
b
),
1
)
),
C,
--TOCOL(
HSTACK(
B(
1,
6
),
B(
3,
6
)
)
),
D,
--MID(
A,
SEQUENCE(
LEN(
A
)
),
1
),
A&10-MOD(
SUM(
D*C
),
10
)
)
)
)Excel solution 7 for Convert ISBN10 to ISBN13 Numbers, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
_a,
LEFT(
978 & x,
12
),
_b,
10 - MOD(
SUM(
WRAPROWS(
MID(
_a,
SEQUENCE(
12
),
1
),
2
) * {1,
3}
),
10
),
_a & _b
)
)
)Excel solution 8 for Convert ISBN10 to ISBN13 Numbers, proposed by LEONARD OCHEA 🇷🇴:
=LET(
s,
SEQUENCE(
12
),
a,
978&A2:A10,
b,
MMULT(
--MID(
a,
TOROW(
s
),
1
),
-1^s+2
),
LEFT(
a,
12
)&10-MOD(
b,
10
)
)Excel solution 9 for Convert ISBN10 to ISBN13 Numbers, proposed by Abdallah Ally:
=MAP(A2:A10,
LAMBDA(u,
LET(a,
u,
b,
LEFT(
978&a,
12
),
c,
REDUCE(
0,
SEQUENCE(
LEN(
b
)
),
LAMBDA(
x,
y,
IF(
ISODD(
y
),
x+MID(
b,
y,
1
),
x+MID(
b,
y,
1
)*3
)
)
),
b&(10-MOD(
c,
10
)))))Excel solution 10 for Convert ISBN10 to ISBN13 Numbers, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(LEFT(
978&A2:A10,
12
),
LAMBDA(r,
LET( s,
SEQUENCE(
LEN(
r
)
),
r&(10-RIGHT(
SUM(
MID(
r,
s,
1
)*IF(
MOD(
s,
2
)=1,
1,
3
)
)
)))))Excel solution 11 for Convert ISBN10 to ISBN13 Numbers, proposed by Julien Lacaze:
=LET(d,
LEFT(
A2:A10,
9
),
p,
978,
m,
MOD(
SEQUENCE(
12,
,
,
2
),
4
),
p&d&(10-MOD(MMULT(--(MID(
p&d,
SEQUENCE(
,
12
),
1
)),
m),
10)))
the "MOD(SEQUENCE(12,,,2),4)Excel solution 12 for Convert ISBN10 to ISBN13 Numbers, proposed by Pieter de Bruijn:
=978&LEFT(A2:A10,9)&10-RIGHT(MMULT(--MID(978&A2:A10,COLUMN(A:L),1),ISODD(ROW(1:12))*-2+3))Excel solution 13 for Convert ISBN10 to ISBN13 Numbers, proposed by Nicolas Micot:
=LET(_chiffres;
STXT(
"978"&A2;
SEQUENCE(
12
);
1
);
_toCols;
ORGA.COLS(
_chiffres;
2
);
_mult;
_toCols*{1;
3};
GAUCHE(
"978"&A2;
12
)&(10-MOD(
SOMME(
_mult
);
10
)))Excel solution 14 for Convert ISBN10 to ISBN13 Numbers, proposed by Ziad A.:
=MAP(
LEFT(
978&A2:A10,
12
),
LAMBDA(
a,
a&10-MOD(
SUM(
MID(
a,
SEQUENCE(
6,
2
),
1
)*{1,
3}
),
10
)
)
)Excel solution 15 for Convert ISBN10 to ISBN13 Numbers, proposed by Giorgi Goderdzishvili:
=
MAP(A2:A10,
LAMBDA(x,
LET(
isb,
x,
cnc,
978&isb,
fr_12,
LEFT(
cnc,
12
),
sm,
SUM(
MID(
fr_12,
SEQUENCE(
,
LEN(
fr_12
)
),
1
)*IF(
ISODD(
--SEQUENCE(
,
LEN(
fr_12
)
)
),
1,
3
)
),
md,
10-MOD(
sm,
10
),
1*(fr_12&md))))Excel solution 16 for Convert ISBN10 to ISBN13 Numbers, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(
A2:A10,
LAMBDA(
a,
"978"&LEFT(
a,
9
)&10-MOD(
SUM(
MID(
REPT(
"13",
6
),
SEQUENCE(
12
),
1
)*MID(
"978"&LEFT(
a,
9
),
SEQUENCE(
12
),
1
)
),
10
)
)
)Excel solution 17 for Convert ISBN10 to ISBN13 Numbers, proposed by Hazem Hassan:
=MAP(A2:A10,
LAMBDA(x,
LET(s,
10-LEN(
x
),
y,
IF((s)=0,
x,
REPT(0,
(s))&x),
d,
LEFT(
978&y,
12
),
f,
SEQUENCE(
12
),
z,
MID(
d,
f,
1
),
d&10-MOD(
SUM(
TOCOL(
IF(
ISEVEN(
--f
),
z*3,
z*1
),
3
)
),
10
))))Solving the challenge of Convert ISBN10 to ISBN13 Numbers with Python in Excel
Python in Excel solution 1 for Convert ISBN10 to ISBN13 Numbers, proposed by John V.:
Hi to all!
One (Python) option could be:
b = "978" + str(d)[:9]
return b + str(10 - sum(int(j) * (1 + i % 2 * 2) for i, j in enumerate(b)) % 10)
[c(i) for i in xl("A2:A10")[0]]
Blessings!
Solving the challenge of Convert ISBN10 to ISBN13 Numbers with R
R solution 1 for Convert ISBN10 to ISBN13 Numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(stringr)
library(data.table)
input = read_excel("ISBN10 to ISBN13 Conversion.xlsx", range ="A1:A10")
test = read_excel("ISBN10 to ISBN13 Conversion.xlsx", range ="B1:B10")
convert_isbn <- function(isbn) {
isbn_base <- paste0('978', str_sub(isbn, 1, 9))
digits <- as.integer(unlist(str_split(isbn_base, "")))
multipliers <- map_dbl(seq_along(digits), ~if_else(.x %% 2 == 1, 1, 3))
sum_products <- sum(map2_dbl(digits, multipliers, ~ .x * .y))
check_digit <- (10 - (sum_products %% 10)) %% 10
isbn13 = paste0(isbn_base, check_digit)
return(isbn13)
}
result = input %>%
mutate(isbn13 = map_chr(ISBN10, convert_isbn))
identical(test$`ISBN13 Answer Expected`, result$isbn13)
#> [1] TRUE
&&