Excel Formula To Generate EAN 13 Digit Barcode Check Digit

Posted byYakuPosted inHowTo, OpenBravo, Techie StuffTags:, , , ,

I was preparing data in an Excel spreadsheet for import into an OpenBravoPOS database, and needed to generate check digits for my custom EAN-13 barcodes. 

Here’s the Excel formula I constructed to generate a 13-digit barcode check digit. It works in Google Sheets too!

Click here to skip to straight to the final, monster all-in-one formula, or keep reading to see the how-and-why of the formula’s components.

OpenBravoPOS

I’ve been tinkering with OpenBravoPOS for a while – it’s an exciting learning curve. What isn’t so exciting is the process of stock taking, and click-click-clicking your way a thousand times around the OpenBravoPOS interface.

No fault of OpenBravoPOS, it’s just when you’re starting from scratch with over 500 products (and that’s a smallish inventory), it gets a bit tedious.

An easy workaround is to set up the database structure in Excel. Then, with a little preparation, you can export the data from Excel and import it in to your database.

Barcodes Not in the Wild

To make products scanable, they need barcodes. Some have, some don’t, so I created barcodes for all the products. Manufacturers of products get their barcodes through proper channels, but in my closed environment, where their only purpose is to identify my products when I scan them, made-up barcodes suit me fine.

I chose the EAN-13 Barcode, because OpenBravoPOS comes with a report that can convert a 13 digit number into a ready-to-print EAN-13 barcode image.

The catch is that the 13th digit, which is a check digit, must be correct according to the EAN-13 Check Digit Formula, so you can’t just come up with any 13 numbers. For one, OpenBravoPOS won’t even generate the barcode if the check digit isn’t right.

There are websites which will generate the 13th check digit for you based on any 12 numbers you enter – and that’s fine for only a few barcodes, but when you have 500+ barcodes to generate you’d be back to click-click-clicking your way through hundreds of numbers, copy-and-pasting the check digits. Not cool.

Excel Formula for that 13-Digit Barcode Check Digit

1. You need unique strings of 12 numbers

First, I started with 12 numbers, which has to be unique – in other words each 12 digit string can only be used once in the database.

I did it like this:

  • The date made up the 1st 6 digits: 100810
  • The next 6 digits are just sequential, and Excel will sort that out, so I added 000001
  • In Excel when you drag the cell selector down, it will increment the 1 as you drag it, so every product will have a unique number. You can have 1,000,000 unique barcodes/products with this numbering system per day.

2. Finicky-but-not-complicated Check Digit Formula

In practice, the 13th digit determines whether or not your other 12 numbers in the barcode are correct. I guess this is to verify that the numbers scanned properly. For ease of demonstration, I’ll use 123456789012 as my 12 digit example number (in hindsight this just made things confusing, but nevermind).

  • Starting with,and including, the 2nd digit, take every other digit in your 12 digit number, add them all together and times by 3. In my example:
  • (2 + 4 + 6 + 8 + 0  + 2) * 3 = 66
  • In Excel / Google Sheets use the MID() function to select the specific digits. If your 12 digit number is in cell C4 (column C row 4) you can add the following to any other cell to see the result:
  • =MID(C4, 2, 1)
  • It takes the 2 as the digit position you want, and the 1 indicates how many digits starting from that position you want – in our case we want just 1 digit at a time, starting at position no. 2, then position no. 4, etc. To perform this part of the calculation, the Excel / Google Sheets formula looks like this:
  • =(MID(C4,2,1)+MID(C4,4,1)+MID(C4,6,1)+MID(C4,8,1)+MID(C4,10,1)+MID(C4,12,1))*3
  • Next, take the remaining digits and add them together. The actual formula says * by 1, but am I missing something, or is that pointless? So I ignore the * 1 bit in my example:
  • 1 + 3 + 5 + 7 + 9 + 1 = 26
  • In Excel / Google Sheets, use the same MID() function to select these digits. This part of the calculation’s Excel / Google Sheets formula looks like so:
  • =MID(C4,1,1)+MID(C4,3,1)+MID(C4,5,1)+MID(C4,7,1)+MID(C4,9,1)+MID(C4,11,1)
  • Now take the individual sums of those equations, and add them together: 66 + 26 = 92 (I’m sure you know this Excel formula); In case you don’t, in excel it looks like this:
  • =66+26 or by cell number =C5+C6 (assuming you pasted the first formula in C5 and the second one in C6).
  • Next, round the result up to the nearest 10. Our example’s result of rounding up from 92 is 100; In Excel / Google Sheets we use the ROUNDUP() formula for this. Usually ROUNDUP is used for digits to the right of the decimal point, but we can also use it for whole numbers like so:
  • =ROUNDUP(G4,-1)
  • where G4 is the cell location of our un-rounded sum (92), and -1 just tells the function to round up to the nearest 10 (if the decimal mark is position 0, -1 means one step to the left). The actual check digit is your rounded up number, minus the un-rounded sum; so
  • 100 – 92 = 8
  • Thus, our 13th and check digit for this fictitious barcode is 8. Lastly, automatically add (aka concatenate) your check digit with your 12 digit number with the Excel / Google Sheets formula

    =C4&H4

    (assuming your 12 digit number is in cell C4 and your check digit is in cell H4). The result is your 13 digit barcode added at the end of your 12 digit string: 1234567890128.

The Monster all-in-one Excel Formula

If you want minimal columns in your Excel / Google Sheets spreadsheet, then you need just 1 column with your 12 digit number (in my case column C), and another column where you can paste this monster formula, which is everything I explained above in 1 long line:

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

And that’s how I used an Excel formula to generate a 13 digit barcode check digit.

Over the years many others, who found this page looking for this solution, have contributed solutions of their own. The modified formulas below are mostly translationsions into other languages, or tweaked for specific requirements. Have a look:

Contributions from the Comments

Here are some useful varitions on the above that commenters contributed in the comments over the years since this article was originally published:

Ankan’s Swedish Translations:

To get in working in Swedish Ankan suggested the following modifactions (changing the , to ; might have been specific to their setup):

=C4&(AVRUNDA.UPPÅT(((EXTEXT(C4;2;1)+EXTEXT(C4;4;1)+EXTEXT(C4;6;1)+EXTEXT(C4;8;1)+EXTEXT(C4;10;1)+EXTEXT(C4;12;1))3)+(EXTEXT(C4;1;1)+EXTEXT(C4;3;1)+EXTEXT(C4;5;1)+EXTEXT(C4;7;1)+EXTEXT(C4;9;1)+EXTEXT(C4;11;1));-1)-(((EXTEXT(C4;2;1)+EXTEXT(C4;4;1)+EXTEXT(C4;6;1)+EXTEXT(C4;8;1)+EXTEXT(C4;10;1)+EXTEXT(C4;12;1))3)+(EXTEXT(C4;1;1)+EXTEXT(C4;3;1)+EXTEXT(C4;5;1)+EXTEXT(C4;7;1)+EXTEXT(C4;9;1)+EXTEXT(C4;11;1))))

Ann Karina Robson‘s Danish Translation:

Ann struggled to get the formula working, but when I recreated her example it apparently worked for me with the following translations to Danish:

=C4&(ROND.OP(((MIDT(C4,2,1)+MIDT(C4,4,1)+MIDT(C4,6,1)+MIDT(C4,8,1)+MIDT(C4,10,1)+MIDT(C4,12,1))3)+(MIDT(C4,1,1)+MIDT(C4,3,1)+MIDT(C4,5,1)+MIDT(C4,7,1)+MIDT(C4,9,1)+MIDT(C4,11,1)),-1)-(((MIDT(C4,2,1)+MIDT(C4,4,1)+MIDT(C4,6,1)+MIDT(C4,8,1)+MIDT(C4,10,1)+MIDT(C4,12,1))3)+(MIDT(C4,1,1)+MIDT(C4,3,1)+MIDT(C4,5,1)+MIDT(C4,7,1)+MIDT(C4,9,1)+MIDT(C4,11,1))))

Sylvian’s French Translation:

=C4&(ARRONDI.SUP(((STXT(C4;2;1)+STXT(C4;4;1)+STXT(C4;6;1)+STXT(C4;8;1)+STXT(C4;10;1)+STXT(C4;12;1))3)+(STXT(C4;1;1)+STXT(C4;3;1)+STXT(C4;5;1)+STXT(C4;7;1)+STXT(C4;9;1)+STXT(C4;11;1));-1)-(((STXT(C4;2;1)+STXT(C4;4;1)+STXT(C4;6;1)+STXT(C4;8;1)+STXT(C4;10;1)+STXT(C4;12;1))3)+(STXT(C4;1;1)+STXT(C4;3;1)+STXT(C4;5;1)+STXT(C4;7;1)+STXT(C4;9;1)+STXT(C4;11;1))))

Ersin Coketin’s Turkish Translation:

=(YUKARIYUVARLA(((PARÇAAL(C557;2;1)+PARÇAAL(C557;4;1)+PARÇAAL(C557;6;1)+PARÇAAL(C557;8;1)+PARÇAAL(C557;10;1)+PARÇAAL(C557;12;1))3)+(PARÇAAL(C557;1;1)+PARÇAAL(C557;3;1)+PARÇAAL(C557;5;1)+PARÇAAL(C557;7;1)+PARÇAAL(C557;9;1)+PARÇAAL(C557;11;1));-1))-(((PARÇAAL(C557;2;1)+PARÇAAL(C557;4;1)+PARÇAAL(C557;6;1)+PARÇAAL(C557;8;1)+PARÇAAL(C557;10;1)+PARÇAAL(C557;12;1))3)+(PARÇAAL(C557;1;1)+PARÇAAL(C557;3;1)+PARÇAAL(C557;5;1)+PARÇAAL(C557;7;1)+PARÇAAL(C557;9;1)+PARÇAAL(C557;11;1)))

Roberto’s Brazilian Portuguese Translation

=A2&(ARREDONDAR.PARA.CIMA(((EXT.TEXTO(A2;2;1)+EXT.TEXTO(A2;4;1)+EXT.TEXTO(A2;6;1)+EXT.TEXTO(A2;8;1)+EXT.TEXTO(A2;10;1)+EXT.TEXTO(A2;12;1))3)+(EXT.TEXTO(A2;1;1)+EXT.TEXTO(A2;3;1)+EXT.TEXTO(A2;5;1)+EXT.TEXTO(A2;7;1)+EXT.TEXTO(A2;9;1)+EXT.TEXTO(A2;11;1));-1)-(((EXT.TEXTO(A2;2;1)+EXT.TEXTO(A2;4;1)+EXT.TEXTO(A2;6;1)+EXT.TEXTO(A2;8;1)+EXT.TEXTO(A2;10;1)+EXT.TEXTO(A2;12;1))3)+(EXT.TEXTO(A2;1;1)+EXT.TEXTO(A2;3;1)+EXT.TEXTO(A2;5;1)+EXT.TEXTO(A2;7;1)+EXT.TEXTO(A2;9;1)+EXT.TEXTO(A2;11;1))))

Nicolás’ Spanish Translation

=C4&(REDONDEAR.MAS(((EXTRAE(C4;2;1)+EXTRAE(C4;4;1)+EXTRAE(C4;6;1)+EXTRAE(C4;8;1)+EXTRAE(C4;10;1)+EXTRAE(C4;12;1))3)+(EXTRAE(C4;1;1)+EXTRAE(C4;3;1)+EXTRAE(C4;5;1)+EXTRAE(C4;7;1)+EXTRAE(C4;9;1)+EXTRAE(C4;11;1));-1)-(((EXTRAE(C4;2;1)+EXTRAE(C4;4;1)+EXTRAE(C4;6;1)+EXTRAE(C4;8;1)+EXTRAE(C4;10;1)+EXTRAE(C4;12;1))3)+(EXTRAE(C4;1;1)+EXTRAE(C4;3;1)+EXTRAE(C4;5;1)+EXTRAE(C4;7;1)+EXTRAE(C4;9;1)+EXTRAE(C4;11;1))))

Lila’s Italian translation for OpenOffice Calc

=C4&(ARROTONDA.ECCESSO(((STRINGA.ESTRAI(C4;2;1)+STRINGA.ESTRAI(C4;4;1)+STRINGA.ESTRAI(C4;6;1)+STRINGA.ESTRAI(C4;8;1)+STRINGA.ESTRAI(C4;10;1)+STRINGA.ESTRAI(C4;12;1))3)+(STRINGA.ESTRAI(C4;1;1)+STRINGA.ESTRAI(C4;3;1)+STRINGA.ESTRAI(C4;5;1)+STRINGA.ESTRAI(C4;7;1)+STRINGA.ESTRAI(C4;9;1)+STRINGA.ESTRAI(C4;11;1));10)-(((STRINGA.ESTRAI(C4;2;1)+STRINGA.ESTRAI(C4;4;1)+STRINGA.ESTRAI(C4;6;1)+STRINGA.ESTRAI(C4;8;1)+STRINGA.ESTRAI(C4;10;1)+STRINGA.ESTRAI(C4;12;1))3)+(STRINGA.ESTRAI(C4;1;1)+STRINGA.ESTRAI(C4;3;1)+STRINGA.ESTRAI(C4;5;1)+STRINGA.ESTRAI(C4;7;1)+STRINGA.ESTRAI(C4;9;1)+STRINGA.ESTRAI(C4;11;1))))

Published by Yaku

Yaku is a brewer, baker, and semi-retired trouble maker (semi-retired from trouble-making that is). Although he believes anything is possible, he is nevertheless frequently stupefied by his world and the people in it.

69 thoughts on “Excel Formula To Generate EAN 13 Digit Barcode Check Digit”

  1. PL-pl Polish Polski

    =C4&(ZAOKR.GÓRA(((FRAGMENT.TEKSTU(C4;2;1)+FRAGMENT.TEKSTU(C4;4;1)+FRAGMENT.TEKSTU(C4;6;1)+FRAGMENT.TEKSTU(C4;8;1)+FRAGMENT.TEKSTU(C4;10;1)+FRAGMENT.TEKSTU(C4;12;1))*3)+(FRAGMENT.TEKSTU(C4;1;1)+FRAGMENT.TEKSTU(C4;3;1)+FRAGMENT.TEKSTU(C4;5;1)+FRAGMENT.TEKSTU(C4;7;1)+FRAGMENT.TEKSTU(C4;9;1)+FRAGMENT.TEKSTU(C4;11;1));-1)-(((FRAGMENT.TEKSTU(C4;2;1)+FRAGMENT.TEKSTU(C4;4;1)+FRAGMENT.TEKSTU(C4;6;1)+FRAGMENT.TEKSTU(C4;8;1)+FRAGMENT.TEKSTU(C4;10;1)+FRAGMENT.TEKSTU(C4;12;1))*3)+(FRAGMENT.TEKSTU(C4;1;1)+FRAGMENT.TEKSTU(C4;3;1)+FRAGMENT.TEKSTU(C4;5;1)+FRAGMENT.TEKSTU(C4;7;1)+FRAGMENT.TEKSTU(C4;9;1)+FRAGMENT.TEKSTU(C4;11;1))))

    Reply

  2. Muchas gracias.

    Versión en español:

    =C4&(REDONDEAR.MAS(((EXTRAE(C4;2;1)+EXTRAE(C4;4;1)+EXTRAE(C4;6;1)+EXTRAE(C4;8;1)+EXTRAE(C4;10;1)+EXTRAE(C4;12;1))*3)+(EXTRAE(C4;1;1)+EXTRAE(C4;3;1)+EXTRAE(C4;5;1)+EXTRAE(C4;7;1)+EXTRAE(C4;9;1)+EXTRAE(C4;11;1));-1)-(((EXTRAE(C4;2;1)+EXTRAE(C4;4;1)+EXTRAE(C4;6;1)+EXTRAE(C4;8;1)+EXTRAE(C4;10;1)+EXTRAE(C4;12;1))*3)+(EXTRAE(C4;1;1)+EXTRAE(C4;3;1)+EXTRAE(C4;5;1)+EXTRAE(C4;7;1)+EXTRAE(C4;9;1)+EXTRAE(C4;11;1))))

    Saludos 🙂

    Reply

  4. Maravilhoso! Muito obrigado!

    No Excel 16.41 para Mac em portugues do Brasil a fórmula final:

    =A2&(ARREDONDAR.PARA.CIMA(((EXT.TEXTO(A2;2;1)+EXT.TEXTO(A2;4;1)+EXT.TEXTO(A2;6;1)+EXT.TEXTO(A2;8;1)+EXT.TEXTO(A2;10;1)+EXT.TEXTO(A2;12;1))*3)+(EXT.TEXTO(A2;1;1)+EXT.TEXTO(A2;3;1)+EXT.TEXTO(A2;5;1)+EXT.TEXTO(A2;7;1)+EXT.TEXTO(A2;9;1)+EXT.TEXTO(A2;11;1));-1)-(((EXT.TEXTO(A2;2;1)+EXT.TEXTO(A2;4;1)+EXT.TEXTO(A2;6;1)+EXT.TEXTO(A2;8;1)+EXT.TEXTO(A2;10;1)+EXT.TEXTO(A2;12;1))*3)+(EXT.TEXTO(A2;1;1)+EXT.TEXTO(A2;3;1)+EXT.TEXTO(A2;5;1)+EXT.TEXTO(A2;7;1)+EXT.TEXTO(A2;9;1)+EXT.TEXTO(A2;11;1))))

    Reply

  5. Turkish Version:

    (Cell Adresses can be changed from “C” to whatever you use)

    =(YUKARIYUVARLA(((PARÇAAL(C557;2;1)+PARÇAAL(C557;4;1)+PARÇAAL(C557;6;1)+PARÇAAL(C557;8;1)+PARÇAAL(C557;10;1)+PARÇAAL(C557;12;1))*3)+(PARÇAAL(C557;1;1)+PARÇAAL(C557;3;1)+PARÇAAL(C557;5;1)+PARÇAAL(C557;7;1)+PARÇAAL(C557;9;1)+PARÇAAL(C557;11;1));-1))-(((PARÇAAL(C557;2;1)+PARÇAAL(C557;4;1)+PARÇAAL(C557;6;1)+PARÇAAL(C557;8;1)+PARÇAAL(C557;10;1)+PARÇAAL(C557;12;1))*3)+(PARÇAAL(C557;1;1)+PARÇAAL(C557;3;1)+PARÇAAL(C557;5;1)+PARÇAAL(C557;7;1)+PARÇAAL(C557;9;1)+PARÇAAL(C557;11;1)))

    Reply

  6. Thank you, thank you, thank you!!! you saved me hours of work… Legend

    Reply

  7. Is there a way to generate EAN-14 from a EAN 13 number?

    Or at least a EAN 14 generation would help a lot already

    Reply

  8. I have a large list of complete EAN 13 numbers (thus numbers of 13 digits) in column A and I like to take away the check digit so that the remaining 12 digits are listed in column B.

    With what formula can I achieved that?

    Reply

    1. Hi Peter, in PHP it’s called substring, in Excel you can use LEFT(A1, 12) in column B for instance. It means from the left in cell A1, show the first 12 characters.

      Reply

  9. OK thanks a lot too. Here is fhe French version of it :
    =C4&(ARRONDI.SUP(((STXT(C4;2;1)+STXT(C4;4;1)+STXT(C4;6;1)+STXT(C4;8;1)+STXT(C4;10;1)+STXT(C4;12;1))*3)+(STXT(C4;1;1)+STXT(C4;3;1)+STXT(C4;5;1)+STXT(C4;7;1)+STXT(C4;9;1)+STXT(C4;11;1));-1)-(((STXT(C4;2;1)+STXT(C4;4;1)+STXT(C4;6;1)+STXT(C4;8;1)+STXT(C4;10;1)+STXT(C4;12;1))*3)+(STXT(C4;1;1)+STXT(C4;3;1)+STXT(C4;5;1)+STXT(C4;7;1)+STXT(C4;9;1)+STXT(C4;11;1))))

    Reply

  10. If only I’d seen this before I had gone through a lengthy process of developing my own Excel check digit calculation (mine works, but is nowhere near as elegant)

    But just to complicate things, I also need to find a formula for the price validation digit (I am working with EAN13 barcodes that have 4-digit price immediately before the check digit, and the digit before the price is evidently a price validation somehow calculated from the price)

    🙁 Lengthy google searches have thus far failed to identify any formula that consistently generates the same result as the barcode printer I am attempting to program a replacement for…

    Reply

    1. Hi Grant,

      I’m intrigued. Can you post 3 or 5 samples of your prices and the corresponding barcodes it created? Might be an interesting puzzle challenge.

      Reply

    1. Sorry for the missing word on my previous question, I meant Do you know how to do this Excel formula in MS Access 2013?

      Reply

  12. In case anyone was wondering for UPC-A check digit formula

    =10-MOD((SUM(MID(I3,1,1)+MID(I3,3,1)+MID(I3,5,1)+MID(I3,7,1)
    +MID(I3,9,1)+MID(I3,11,1))*3)+SUM(MID(I3,2,1)+MID(I3,4,1)
    +MID(I3,6,1)+MID(I3,8,1)+MID(I3,10,1)),10)

    Reply

  14. This approach only works if my first digit (from left to right) is different than zero, otherwise I get #VALUE as result. Did anyone have the same issue?

    Reply

  17. The way I do it is here:

    =CONCATENATE(A16,IF(MOD(3*(MID(A16,1,1)+MID(A16,3,1)+MID(A16,5,1)+MID(A16,7,1)+MID(A16,9,1)+MID(A16,11,1))+MID(A16,2,1)+MID(A16,4,1)+MID(A16,6,1)+MID(A16,8,1)+MID(A16,10,1),10)=0,0,10-MOD(3*(MID(A16,1,1)+MID(A16,3,1)+MID(A16,5,1)+MID(A16,7,1)+MID(A16,9,1)+MID(A16,11,1))+MID(A16,2,1)+MID(A16,4,1)+MID(A16,6,1)+MID(A16,8,1)+MID(A16,10,1),10)))

    And if I don’t want to see the N/A

    Reply

    1. You could also try a MOD or a RIGHT(TEXT(##,0),1) on the result, rather than using IF and having to repeat the MOD of the SUM of the MIDs

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

      Hope that helps or is at least interesting!

      Reply

  20. Thank you SOOO much for sharing this, I really needed this for work as I was struggling to find out how check no. was determined. You’re my excel hero! 🙂

    Reply

  21. Thanks for this. I implemented it and it works great… but for my project I need to calculate the check digit for UPC-A (12-digit) barcodes given the first 11 digits.

    Do I just edit your formula to eliminate references to MID(C4,12,1)?

    Reply

    1. The UPC-A is similar, but a wee bit different. For one, the first part of the formula starts with the digit in position 1, where EAN-13 stars with the digit in position 2. So you will have to change the even numbers in MID() to odd numbers, ie. MID(C4,2,1) to MID(C4,1,1). This WIKIPEDIA page explains it further with an example. Not sure of the last operation is a shortcut to what I did, but try it out with their example and see if it works.

      Reply

  22. Thank you very much for this post, especially for tanking the care of explaining each part. This was really useful for me since I use libre office calc in Italian, so I had to transform and TRANSLATE the functions! Here is my final formula
    =C4&(ARROTONDA.ECCESSO(((STRINGA.ESTRAI(C4;2;1)+STRINGA.ESTRAI(C4;4;1)+
    STRINGA.ESTRAI(C4;6;1)+STRINGA.ESTRAI(C4;8;1)+STRINGA.ESTRAI(C4;10;1)+
    STRINGA.ESTRAI(C4;12;1))*3)+(STRINGA.ESTRAI(C4;1;1)+STRINGA.ESTRAI(C4;3;1)+
    STRINGA.ESTRAI(C4;5;1)+STRINGA.ESTRAI(C4;7;1)+STRINGA.ESTRAI(C4;9;1)+
    STRINGA.ESTRAI(C4;11;1));10)-(((STRINGA.ESTRAI(C4;2;1)+
    STRINGA.ESTRAI(C4;4;1)+STRINGA.ESTRAI(C4;6;1)+STRINGA.ESTRAI(C4;8;1)+
    STRINGA.ESTRAI(C4;10;1)+STRINGA.ESTRAI(C4;12;1))*3)+(STRINGA.ESTRAI(C4;1;1)+
    STRINGA.ESTRAI(C4;3;1)+STRINGA.ESTRAI(C4;5;1)+STRINGA.ESTRAI(C4;7;1)+
    STRINGA.ESTRAI(C4;9;1)+STRINGA.ESTRAI(C4;11;1))))

    Reply

  25. This is a total life saver! I was manually entering the codes for the 13th check digit. This formula rocks!!!
    I copied and pasted the formula on Word and used FIND to replace C4 with my desired Excel Cell. Wahla! A whole column of 13th check digit created in seconds. Love you!

    Reply

  26. Thanx for the help. Really useful. It works great in Excel 2013. Just change “,” for “;” and “C4” for “$C4”. and from here just copy and paste.

    Reply

  27. Thank you. I just replaced the , to ; and it helped me a lot on creating an EAN13 validation field.

    Reply

  29. 1Earth I want to kiss you, you are sooo AWESOME!!! Thank you so very much.

    I’d been gooling everything from Excel Formula to make a barcode text to simple excel barcode generator and after 50 useless pages I found this one and it is easy peasy! Thank you so much. I’m so glad I didn’t have to download a program and it just worked beautifully.

    Thank you so much!!!

    Reply

  30. Thank you so much, i translated this formula to dutch and it worked perfectly,
    you saved me so much time and errors,

    Best wishes from Belgium

    Reply

  31. What would the formula be for EAN-8 barcodes? I have 7 digit student numbers and need to calculate the 8th digit for the student barcodes… I'm not familiar enough with it to convert the above formula to EAN-8. Can you help?

    Reply

    2. Ok, it works exactly like the EAN-13 – so here's the formula, edited to work with the EAN-8:

      =C4&(ROUNDUP(((MID(C4,1,1)+MID(C4,3,1)
      +MID(C4,5,1)+MID(C4,7,1))*3)
      +(MID(C4,2,1)+MID(C4,4,1)+MID(C4,6,1)),-1)-(((MID(C4,1,1)+MID(C4,3,1)
      +MID(C4,5,1)+MID(C4,7,1))*3)
      +(MID(C4,2,1)+MID(C4,4,1)+MID(C4,6,1))))

      Reply

  32. I am trying to use your formula, but I just get an error saying it is not working. I have translated the formula names into Danish as I am using a Danish version of Excel. I have your example 123456789012 in column C in row 4 and then I place the cursor in column D in row 4. After this, I paste the formular at the top and get the error saying that the formula is not working. It highlights this: C4,2,1

    Can you explain it for dummies? 🙂

    Reply

    1. Hi Ann, what you did sounds like it should have worked – I followed your instructions to see if I could recreate the error, but it worked 🙂

      In Danish MID becomes MIDT and ROUNUP becomes RUND.OP

      Hope you can make it work.

      Reply

  33. I'm sending you a sunny greetings from Slovenia!
    You saved me from a lot of thinking, calculating,…

    Thank you for this help with formula!

    Aleš

    Reply

  36. I think I saved a few parentheses with this where D4 is the barcode less check digit:

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

    Steve

    Reply

    1. That doesn’t work correctly, as if the reslut of the ‘MOD…’ part of the equation is ‘0’ then the formula adds ’10’ to the end of the barcode.

      Reply

    2. This is great, focused on the remainder (MOD,10). I just wrapped another MOD around it to eliminate the 10/0 issue:

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

      So if the sum of all the MIDs is 60, the original MOD remainder is 0, so 10-0=10, which the MOD I’ve added makes 0.

      Hope that helps someone – and keeps the post alive after nearly a decade!
      Cheers
      Danny

      Reply

  37. thanks for lot of information

    i have a shop and i need to switch to open bravo pos so i need to store all product barcode in it, can you guide me to do it one time becouse its hard to store product one by one and also i dont have experience in MySQL.

    Reply

  38. Take you for taking the time out to provide this information. Saved me tons of time….

    Reply

    1. Not sure about 7 digits, but there is probably some other formula for generating its check digit. Google is your friend 🙂

      Reply

  42. thanx:)

    worked fine in Excel 2002 (2000), after I translated into swedish (MID to EXTEXT, and ROUNDUP to AVRUNDA.UPPÅT) and turned the (,) to (;)

    Reply

  44. Hey thanks for your help but can you please explain how you can do it in an Excel 2010 sheet as you have explained about re-generating them from your excel sheet into database but i need to generate them in an excel sheet.. Is there any formula or free software available?? please suggest.

    Thanks.

    Reply

    1. Hi, I don't understand what you mean. This formula is exactly for generating it in an Excel spreadsheet. Wether or not you import into a database is irrelevant, the article above describes the formula for generating the code in Excel.

      Reply

  45. Great great great Help!!! Thanks!!! Very good!
    I used openoffice calc. I only have to switch "," to ";" and it worked perfectly.
    Thank you! =)

    Reply

  46. Hi,

    Thank you for this article. I am sure I will run into this very soon. I just installed openbravo and trying to figure out how to import data from Excel into the openbravo system. Could you please tell me what to do after creating the excel file like you said above? Thank you.

    Shan.

    Reply

    1. Hi Shan – I did it by importing a .csv file into my database (which on my OpenBravoPOS install is MySQL (OpenBravoPOS using MySQL instructions).

      First I set up the Excel spreadsheet to reflect the table 'products' from the OpenBravoPOS database. I imported all the data about my products this way, but I guess those who know what they're doing (not me) could probably import only the barcodes too.

      Then, once you've created your barcodes, you "Save As…" the Excel spreadsheet as a comma separated values file (.csv). It's that .csv file that you then import into the database.

      Do this on a test database if you're a noob like me. For importing a .csv into MySQL you can find instructions from the MySQL website here.

      I did this a while ago already and unfortunately I didn't save the exact instructions, but I hope this sorts you out.

      If you are working with MySQL I would suggest getting MySQL Workbench – a visual tool which makes working with the database much easier.

      Reply

  47. Great help – thanks!

    I only wanted the check digit so I used the following instead of taking the sum from the roundup figure, which also works.:

    =MOD(((MID(B4,2,1)+MID(B4,4,1)+MID(B4,6,1)

    +MID(B4,8,1)+MID(B4,10,1)+MID(B4,12,1))*3)+(MID(B4,1,1)+MID(B4,3,1)+MID(B4,5,1)

    +MID(B4,7,1)+MID(B4,9,1)+MID(B4,11,1)

    ),10)

    Reply

    1. I would hope so; it generated all the barcodes for the products in the diveshop I used it for. Glad if it helped you too.

      Reply

  50. 4 june 2011

    I would like to thank the person responsible for this article, having read it I now know how to solve at least part of my problem.

    Up to now I have been using a site on the internet to convert my ISBN numbers, which is great but I means I am adding extra work to my data entry, now at lest I can do it in one direction and how knows with a bit more thought maybe in both direction

    Regards Pete

    Reply

Leave a Reply

%d