Conversion of ISBN13 to ISBN10

ExcelNewbie89

New Member
Joined
Sep 2, 2015
Messages
6
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!
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi,

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

Code:
=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:

[TABLE="width: 121"]
[TR]
[TD]9781682033364[/TD]
[/TR]
[TR]
[TD]9781682033548[/TD]
[/TR]
[TR]
[TD]9781302906740[/TD]
[/TR]
[TR]
[TD]9781302912499[/TD]
[/TR]
[/TABLE]
I get the following figures back back:

[TABLE="width: 794"]
[TR]
[TD]16820333648[/TD]
[/TR]
[TR]
[TD]16820335486[/TD]
[/TR]
[TR]
[TD]13029067407[/TD]
[/TR]
[TR]
[TD]13029124996[/TD]
[/TR]
[TR]
[TD]18434491888[/TD]
[/TR]
[/TABLE]

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!!!
 
Upvote 0
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"))
 
Upvote 0
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))))
 
Last edited:
Upvote 0
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:

Code:
[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:

Code:
[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]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top