ISBN conversion

An ISBN is the International Standard Book Number. It is a unique identifier for books that is accompanied by a barcode and part of the book cover design of all books for sale. ISBNs were originally 10 digits long, but like telephone numbers we have started running out of numbers.

Beginning on January 1, 2007, all the 10 digit ISBNs will be converted to 13 digit ISBNs. While you can do this one at a time for free online at various websites, to convert 400 numbers all at once will cost you. Or, you could use the handy dandy formula that my friend Anthony came up with. This formula worked for me and so I wanted to share it with others who are looking for a way to convert many ISBNs all at once, for free. When I asked Anthony if I could share it, he said, "You can definitely share it with anybody. A couple things I would keep in mind:

1) I'm no expert on ISBN's and spent less than 90 minutes coming up with that. But I did consult the manual.

2) I use the prefix "978" which is also what that website uses. However, according to wikipedia it can in some cases be "979." I don't know what those cases are , but it may be worth looking into.
3) I hard-coded the locations of the hyphens, but in my reading it seems the different components are of variable length (meaning the hyphen's may be in different locations for different ISBN numbers). If I had known that when I sent it to you I would have left out the hyphens entirely, which is what you may want to do."

Anthony's formula:

Cell A3 has the ISBN number - no dashes, 10 characters long

Cell B3 has:
=CONCATENATE("978","-",MID(A3,1,1),"-",MID(A3,2,3),"-",MID(A3,5,5),"-",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))