Conversion of ISBN13 to ISBN10

[ExcelNewbie89]

Hi,

I've seen this thread here but it looks like the link to a working formula doesn't work. Is anyone able to help?

https://www.mrexcel.com/forum/excel-questions/285670-isbn-13-10-conversion-macro.html

What I essentially need to do is the following:

Example

Let us consider the ISBN number 9780345391803: To get its corresponding ISBN-10 code we simply drop the leading 978 and the checksum 3 yielding 034539180.
Next we need to compute the new checksum:
10*0 + 9*3 + 8*4 + 7*5 + 6*3 + 5*9 + 4*1 + 3*8 + 2*0 = 185
The next number divisible by 11 is 187, so the new checksum is 2 and thus the resulting ISBN-10 code 0345391802.

Rules

• Your input will always have a corresponding ISBN-10 number (ie. it is exactly 13 digits long and starts with 978)
• The input doesn't necessarily have to be a valid ISBN-13 (eg. 9780000000002)
• You're guaranteed that the resulting ISBN won't end with X
• You may take input as an integer or string (with or without hyphens) however a precomputed list of digits are not allowed
• Your output must be a valid ISBN-10 number (with or without hyphens)
• Your output may be an integer or string (again no lists of digits)

example:
The check digit is the last digit of an ISBN. It is calculated on a modulus 11 with weights 10-2, using X in lieu of 10 where ten would occur as a check digit.

This means that each of the first nine digits of the ISBN – excluding the check digit itself – is multiplied by a number ranging from 10 to 2 and that the resulting sum of the products, plus the check digit, must be divisible by 11 without a remainder.
For example ISBN 0843610727


ISBN No. : 0 8 4 3 6 1 0 7 2 7
Weight : 10 9 8 7 6 5 4 3 2


i.e.(0*10 + 8*9 +4 *8 + 3*7 + 6*6 + 1*5 + 0*4 + 7*3 + 2*2 + 7)
--------------------------------------------------------------------------------

Products 0 +72 +32 +21 +36 +5 +0 +21 +4 +7


Total: 198

As 198 can be divided by 11 without remainder 0-8436-1072-7 is a valid ISBN.
7 is the valid check digit.

Is there a way to have this written in a formula maybe or even a Macro?

Thank you!

 

[ExcelNewbie89]

Hi,

I seem to have found this on this forum from user pgc01 in 2007:

=SUBSTITUTE(TRIM(SUBSTITUTE(MID(A2,4,LEN(A2)-4),"-"," "))," ","-")&IF(MID(A2,LEN(A2)-1,1)="-","-","")&SUBSTITUTE(MOD(990-SUMPRODUCT((11-ROW($1:$9))*MID(MID(SUBSTITUTE(A2,"-",""),4,9),ROW($1:$9),1)),11),10,"X")

If I copy this formula into Column B, into my spreadsheet with ISBN13 in Column A:

9781682033364

9781682033548

9781302906740

9781302912499

I get the following figures back back:

16820333648

16820335486

13029067407

13029124996

18434491888

If I use an online converter like: http://pcn.loc.gov/isbncnvt.html and check them, I get the following back:

1682033368
1682033546
1302906747
1302912496
1843449188

So I'm not 100% convinced it's correct and it's above my understanding on Excel.

Any help is greatly appreciated!!!

 

[ExcelNewbie89]

Figured out why it didn't work, I had blanks in my Column A so I edited the formula with the following:

=TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(MID(A2,4,LEN(A2)-4),"-"," "))," ","-")&IF(MID(A2,LEN(A2)-1,1)="-","-","")&SUBSTITUTE(MOD(990-SUMPRODUCT((11-ROW($1:$9))*MID(MID(SUBSTITUTE(A2,"-",""),4,9),ROW($1:$9),1)),11),10,"X"))

 

[Special-K99]

Does this work for you?

=CEILING(SUMPRODUCT(--((11-ROW(1:9))*(MID(MID(A1,4,10),ROW(1:9),1)))),11)-SUMPRODUCT(--((11-ROW(1:9))*(MID(MID(A1,4,10),ROW(1:9),1))))

 

[ExcelNewbie89]

Does this work for you?

=CEILING(SUMPRODUCT(--((11-ROW(1:9))*(MID(MID(A1,4,10),ROW(1:9),1)))),11)-SUMPRODUCT(--((11-ROW(1:9))*(MID(MID(A1,4,10),ROW(1:9),1))))

Hi Special-K99,

Unfortunately not, but this does if you have trailing/leading blanks:

[COLOR=#333333][I]=[/I][/COLOR][SIZE=4][COLOR=#333333][I][B]TRIM([/B][/I][/COLOR][/SIZE][COLOR=#333333][I]SUBSTITUTE(TRIM(SUBSTITUTE(MID(A2,4,LEN(A2)-4),"-"," "))," ","-")&IF(MID(A2,LEN(A2)-1,1)="-","-","")&SUBSTITUTE(MOD(990-SUMPRODUCT((11-ROW($1:$9))*MID(MID(SUBSTITUTE(A2,"-",""),4,9),ROW($1:$9),1)),11),10,"X")[/I][/COLOR][B][SIZE=4])[/SIZE][/B]

If you know you do not have blanks, then you can just use:

[COLOR=#333333][I]=[/I][/COLOR][COLOR=#333333][I]SUBSTITUTE(TRIM(SUBSTITUTE(MID(A2,4,LEN(A2)-4),"-"," "))," ","-")&IF(MID(A2,LEN(A2)-1,1)="-","-","")&SUBSTITUTE(MOD(990-SUMPRODUCT((11-ROW($1:$9))*MID(MID(SUBSTITUTE(A2,"-",""),4,9),ROW($1:$9),1)),11),10,"X")[/I][/COLOR]