ISBN 13 to 10 conversion Macro

[vijayparihar]

I have seen a few formulas online to covert an isbn 10 to isbn 13, like this one (http://ndpsoftware.com/isbn.php) but i was wondering if there is a macro for reverse, i.e. conversion from isbn 13 to 10, using MOD 11 the algorithm is:

Converting from Bookland EAN-13 to a 10-digit ISBN
978-0-393-04002-9
1. Strip the check-digit, as a new modulus 11 check-digit will be generated for the 10-digit ISBN. This gives us 978-0-393-04002-

2. Strip the “978” EAN Bookland prefix, giving a 10-digit ISBN string without its check-digit. ( Stripping the “978” prefix gives us 0-393-04002-

3. The 10th digit of the 10-digit ISBN is the modulo 11 check-digit, which will have a value of 0-10, with the value 10 being represented by “X”.
Modulus 11 algorithm to calculate check digit for the 10-digit ISBN (check digit unknown)
Incomplete 10-digit ISBN = 0-393-04002-?

weightage : (0*10 + 3*9 + 9*8 + 3*7 + 0*6 + 4*5 + 0*4 + 0*3 + 2*2) ie ( 10 * first digit, 9*second & so on till 2* 9th digit)

Check digit = mod11 (11 - mod11 (Product Total)) = mod11 (11 – mod11 (144)) = 10

(Special case note: When the check digit calculates to 10, it is represented as an “X”.)
(Technical note: mod11 of a test number returns the remainder of the test number divided by 11 unless the number is less than 11 in which case it returns the test number itself)
Complete 10-digit ISBN = 0-393-04002-X

would appreciate if some one can help with this or let me know if such formula already exsists. Thanks

 

[Rick Rothstein]

I don't see a thread related to ISBN conversions at the link PGC posted, so I don't know if this duplicates what he posted or not, but here is the formula I came up with...

=MID(A1,5,12)&SUBSTITUTE(MOD(11-MOD(SUMPRODUCT((11-ROW(1:10))*MID(SUBSTITUTE(MID(A1,5,11)&"0","-",""),ROW(1:10),1)),11),11),10,"X")

 

[Rick Rothstein]

I don't see a thread related to ISBN conversions at the link PGC posted, so I don't know if this duplicates what he posted or not, but here is the formula I came up with...

=MID(A1,5,12)&SUBSTITUTE(MOD(11-MOD(SUMPRODUCT((11-ROW(1:10))*MID(SUBSTITUTE(MID(A1,5,11)&"0","-",""),ROW(1:10),1)),11),11),10,"X")

And as for a UDF, a user defined function (you said macro, but I am assuming you meant UDF), give this one a try...

Function ISBN13to10(ISBN13 As String) As String
  Dim X As Long, ISBN As String, Sum As Long
  ISBN = Mid(ISBN13, 5, 12)
  For X = 10 To 2 Step -1
    Sum = Sum + X * Mid(Replace(Mid(ISBN13, 5, 12), "-", ""), 11 - X, 1)
  Next
  ISBN13to10 = ISBN & "-" & Replace((11 - (Sum Mod 11)) Mod 11, 10, "X")
End Function

 

[MadDogVachon]

I am looking for a macro to convert ISBN13 to ISBN10. When I click on the link above it doesn't take me to the macro mentioned. Can anyone help me with this? I have a ton of ISBNs that I need to convert.
Thanks

Hi,

Here it is: Mod 11 formula for valid check digit

MDV

 

[karankashyap]

like not open for me

 

[donwiss]

Here are my macros for this:

Function ISBN10to13(ISBN As String) As String

Dim CheckSum As Integer, i As Integer, Weight As Integer

' drop the last character, and tack on "978" to the front. now 12 digits
ISBN = "978" & Left(ISBN, 9)

' calculate a new check digit to tack on to the end
For i = 1 To 12
If 1 = (i Mod 2) Then
Weight = 1
Else
Weight = 3
End If
CheckSum = CheckSum + Mid(ISBN, i, 1) * Weight
Next i

' Mod 10 remainder and Mod 11 checkdigit
ISBN10to13 = ISBN & (10 - CheckSum Mod 10) Mod 10

End Function

Function ISBN13to10(ISBN As String) As String

Dim CheckSum As Integer, i As Integer

' drop the first three and last character. now 9 digits
ISBN = Mid(ISBN, 4, 9)

' calculate a new check digit to tack on to the end
For i = 1 To 9
CheckSum = CheckSum + Mid(ISBN, i, 1) * (11 - i)
Next i
CheckSum = 11 - (CheckSum Mod 11)

If CheckSum = 10 Then
ISBN13to10 = ISBN & "X"
ElseIf CheckSum = 11 Then
ISBN13to10 = ISBN & "0"
Else
ISBN13to10 = ISBN & CheckSum
End If

End Function