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:
Is there a way to have this written in a formula maybe or even a Macro?
Thank you!
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: