 # Generating EAN/ISBN-13 Check Digits in Excel

When you’re dealing with ISBN-13’s you’ll sometimes need to generate a check digit, either to verify  a number your employees are inputting during inventory, or scanning off a book or product, or possibly you’re receiving input from a publisher which uses the antiquated ISBN-10 standard and you need to convert it to ISBN-13’s.

### ISBN-10 Check Digits

First, what is a check digit? On most barcodes or standardized numbers the last character is what’s called a check digit. This number is generated by using a formula on all the other numbers. In ISBN-10’s this number is generated by multiplying each of the first 9 digits by decreasing numbers starting with 10. For example, the number 0330258648 is a valid ISBN-10. To generate the check digit (which in this example is the 8 at the end of the ISBN-10) we use the following formula:

``((0*10)+(3*9)+(3*8)+(0*7)+(2*6)+(5*5)+(8*4)+(6*3)+(4*2)) mod 11 = 3 11-3 = 8``

What’s going on here is that each of the first 9 digits of the ISBN-10 are being multiplied by a descending sequence of numbers from 10 to 2. The sum of these multiplications are then taken and divided by 11 with the remainder being take (using modular arthimatic here). The remainder plus the check digit must equal 11 to be valid so to find the check digit subtract the remainder from 11. If the result is 11 (11-0=11), the check digit is written as an upper-case “X”.

### ISBN-13 Check Digits

Check digit calculation with ISBN-13s is somewhat different. To find a check digit for a certain ISBN-13, for example 9780395489314, you can use the following formula:

``````
((9*1)+(7*3)+(8*1)+(0*3)+(3*1)+(9*3)+(5*1)+(4*3)+(8*1)+(9*3)+(3*1)+(1*3)) mod 10 = 6
10-6 = 4
``````

This works very similarly except that the initial sum is developed by multiplying the first 12 digits of the ISBN-13 by 1 and 3 alternately and then using mod 10 instead of mod 11 do find the remainder of that sum. After that, again, the check digit + the remainder = 10 so 10 – the remainder = the check digit. In this case 4.

### Performing the calculations in Excel

To do this, you’ll need to use the following formula:

``````
=B2&(ROUNDUP(((MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1)+MID(B2,12,1))*3)+(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1)),-1)-(((MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1)+MID(B2,12,1))*3)+(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1))))
``````

You’ll notice that this formula refers to cell B2. In cell B2 you’ll need to have one of the following:

1. The first 12 digits of an ISBN-13 (A useful function here is =LEFT(B1,12) which will simply take the first 12 characters of cell B1).
2. The first 9 digits of an ISBN-10 with “978” concatenated to the front of it (e.g. 978033025864)

And that’s how to generate a ISBN-13 check digit in Excel!

### Notes

1. Remember that an ISBN-13 is the same as an ISBN-10 with “978” prepended and a different check digit.
2. If you’re validating an ISBN-13 which has all 13 digits, don’t forget to remove the checkdigit before passing it through the Excel formula, otherwise you’ll end up with an ISBN-14, which doesn’t exist and is definitely not valid.

## 4 thoughts on “Generating EAN/ISBN-13 Check Digits in Excel”

1. Yuriy Yeremenko says:

This was very helpful, thank you Daniel.

2. Stan says:

In the 10 to 13 conversion, if the result for the check digit is 11 (11-0=11), the end result is a zero. If it’s 10 (11-1=10), the result is X.

3. Stan says:

Sorry, that’s the 13-to-10 conversion.

4. Chris Kull says:

Thanks Dan! We need such wizards! What i like about your formula is that it is not tied to a certain number of lines as many others are.