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

 

[vijayparihar]

Any excel guru's wana take a shot on this mod 11 formula ??????????????

 

[vijayparihar]

any one for the macro

 

[R-D]

Try the following, assumes your 13-digit ISBN is in cell A1. Paste this in B1 and fill down.


=MID(A1,5,12)&IF(11-(MOD(SUM(LEFT(MID(A1,5,12),1)*10,MID(MID(A1,5,12),3,1)*9,MID(MID(A1,5,12),4,1)*8,MID(MID(A1,5,12),5,1)*7,MID(MID(A1,5,12),7,1)*6,MID(MID(A1,5,12),8,1)*5,MID(MID(A1,5,12),9,1)*4,MID(MID(A1,5,12),10,1)*3,MID(MID(A1,5,12),11,1)*2),11))=10,"X",11-(MOD(SUM(LEFT(MID(A1,5,12),1)*10,MID(MID(A1,5,12),3,1)*9,MID(MID(A1,5,12),4,1)*8,MID(MID(A1,5,12),5,1)*7,MID(MID(A1,5,12),7,1)*6,MID(MID(A1,5,12),8,1)*5,MID(MID(A1,5,12),9,1)*4,MID(MID(A1,5,12),10,1)*3,MID(MID(A1,5,12),11,1)*2),11)))

Its very quick, and very dirty, but it'll get the job done if they're all in the same format you provided.

 

[vijayparihar]

doesnt work, neither on this no.(in example) nor on any other isbn i have . returns invalid value error

 

[pgc01]

Hi vijayparihar

I gave you an answer yesterday, in another post. Did you test it?


http://www.mrexcel.com/board2/viewtopic.php?t=297475&postdays=0&postorder=asc&&start=20

 

[vijayparihar]

wow man,. that seems to be working, though i need to tet it extensively, it works so far

Thanks a ton, did you develop it or found some where online?

i really appreciate that man, thanks a ton. i believe it does the check digit validation too right?

if u found this one online can you check if a check digit (10th digit nfrom 9) for isbn validation formula is there too?

thanks a lot again bro

Hi vijayparihar

I gave you an answer yesterday, in another post. Did you test it?


http://www.mrexcel.com/board2/viewtopic.php?t=297475&postdays=0&postorder=asc&&start=20

 

[pgc01]

did you develop it or found some where online? ... can you check if a check digit (10th digit nfrom 9) for isbn validation formula is there too?

No, I didn't find the formula online. I just used the information you posted.

 

[njk]

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

 

[shg]

See https://www.box.com/s/zgm8gir72i831zl2ev3z

It's a .bas file. Download it to your computer, then drag it into the VBA project.

      ----------A----------- ----------B----------- ------------C------------
  1           Input                  Result                  Formula         
  2   ISBN 978-0-11-000222-4 ISBN 0-11-000222-9     B2: =ConvertISBN(A2)     
  3            9780110002224 ISBN 0-11-000222-9     B3: =ConvertISBN(A3)     
  4   ISBN 0-11-000222-9     ISBN 978-0-11-000222-4 B4: =ConvertISBN(A4, 978)
  5                110002229 ISBN 978-0-11-000222-4 B5: =ConvertISBN(A5, 978)

It's very lightly tested, so holler if it breaks.