Converting 13 Digit ISBNs to 10 (And Vice Versa)

The formulas for this, which will work in LibreOffice (or OpenOffice) Calc, are to be found at User Offline: How to write a spreadsheet formula for ISBN-10 to ISBN-13 and ASIN. Considering that post is dated in 2008, I thought it worthwhile to add another link.

I wanted to do this, but to ISBNs with the dashes included. The resulting code is (13 to 10 digit):


=IF(LEN(H229)>10,CONCATENATE(MID(H229,5,12),IF(N229=10,"X",N229)),H229)


This code removes the 978 prefix. For the full ISBN you need the check digit, which is done thus:


=IF(LEN(H229)>10,MOD(MID(H229,5,1)+MID(H229,7,1)*2+MID(H229,8,1)*3+MID(H229,9,1)*4+MID(H229,10,1)*5+MID(H229,11,1)*6+MID(H229,13,1)*7+MID(H229,14,1)*8+MID(H229,15,1)*9,11),"")


I am either an unkind or a lazy person, and thus did not format them prettily as did the source author. Also, you’d need to modify his code for the reverse conversion.

This is copied from my spreadsheet, in which I used column N for the check digit, and was converting a column of 13 digit ISBNs that were in column H.