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.

1 comment

  1. It worked for me, added 12 numbers in a a2 cell. Your formula created 13th number in a b2 cell and then I applied formula =A2&””&B2 to combine the cell. Thank you so so much

Leave a comment

Your email address will not be published. Required fields are marked *