ISBN 13 to 10 conversion Macro

vijayparihar

New Member
Joined
Oct 18, 2007
Messages
16
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
See https://www.box.com/s/zgm8gir72i831zl2ev3z

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

Code:
      ----------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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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