ISBN Converter: ISBN-10 to ISBN-13
Out of Date
The following content is likely out-of-date. It concerns a legacy technology and/or is no longer maintained-- likely a decade old. Please see you favorite search engine for the latest information.
Intro
This tidbit is from my coworker Anthony Roy. He thought it might be useful to other people, so I'm posting it here. Please send him kudos and questions.
The Macro
Assuming Cell A3 has the ISBN-10 number - no hyphens, 10 characters long, the following formula will produce the corresponding ISBN-13:
=CONCATENATE("978",MID(A3,1,9),MOD((10-MOD(SUM(9, 21, 8, PRODUCT(MID(A3,1,1),3), MID(A3,2,1), PRODUCT(MID(A3,3,1),3), MID(A3,4,1), PRODUCT(MID(A3,5,1),3), MID(A3,6,1), PRODUCT(MID(A3,7,1),3), MID(A3,8,1), PRODUCT(MID(A3,9,1),3)),10)), 10))
I prepend 978 to the first 9 digits of the ISBN, then calculate the check digit.
Several people have pointed out that 10-digit ISBN numbers can start with a zero, but Excel by default drops leading 0's. To prevent this, either start the number with an apostrophe (for example '0446528056), or change the format of the cell (or entire row or column containing the ISBN-10 numbers) to be Text. In Excel 2003, select the cell(s) then Format -> Cells -> Number (Category) -> Text. This will force Excel to preserve the number exactly how you enter it.
Updated Dec 2006, Anthony Roy