EAN13 Barcode checkdigit calculation in Excel

Once upon a time, I wrote a formula to calculate the EAN13 barcode check digit in excel. I happened to mention it on a mailing list and it seems some people are looking for just such a thing and they email me asking about it (which is rather nice).

Anyway, I thought I would post the formula here for public digestion.

It’s not pretty and I believe there is probably a much nicer way of doing it… I just don’t know that way. So here it is:

A2 is cell containing the number of the barcode.

=MOD(10-MOD(MID(TEXT(A2,0),1,1) * 1
+ MID(TEXT(A2,0),2,1) * 3
+ MID(TEXT(A2,0),3,1) * 1
+ MID(TEXT(A2,0),4,1) * 3
+ MID(TEXT(A2,0),5,1) * 1
+ MID(TEXT(A2,0),6,1) * 3
+ MID(TEXT(A2,0),7,1) * 1
+ MID(TEXT(A2,0),8,1) * 3
+ MID(TEXT(A2,0),9,1) * 1
+ MID(TEXT(A2,0),10,1) * 3
+ MID(TEXT(A2,0),11,1) * 1
+ MID(TEXT(A2,0),12,1) * 3,10),10)

I basically convert the text to string to pull out the digit I want to manipulate, then apply the formula to that digit.

I’d love to hear from anyone who knows a more elegant way of doing it.