Excel Formula To Generate 13 Digit Barcode Check Digit

I’m was preparing data in an Excel spreadsheet for import into an OpenBravoPOS database, and I needed to generate check digits for my custom barcodes.  So here’s the Excel formula I came up with to generate a 13-digit barcode check digit.

OpenBravoPOS

I’ve been tinkering with OpenBravoPOS for a while – it’s an awesome and exciting learning curve.

What isn’t so exciting is the actual process of stock taking and click-click-clicking your way a thousand times around the OpenBravoPOS interface to input product names, buy and sell prices, barcodes and stock counts.

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

An easy solution for quicker input is to setup 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 enable products to be scanned, the products in the shop needs barcodes. Some have, some don’t, so I created barcodes for all the products.

If you’re a manufacturer of products, you should get your barcodes through proper channels.  But in my closed environment where the only purpose of the barcodes are to identify my products when I scan them at the POS, made-up barcodes will cause no harm or foul.

I’ve chose the EAN-13 Barcode, because OpenBravoPOS comes with a report that can convert this 13 digit number to a barcode image, which is ready to printed and stuck to your products.

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, because 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.

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

In practice, the 13th digit determines whether or not your the 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 use the MID() function to select the specific digits. If your 12 digit number is in cell C4, you can write MID(C4, 2, 1) – the 2 is the digit position, and the 1 indicates how many digits 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 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 how pointless is that?
  • In my example: 1 + 3 + 5 + 7 + 9 + 1 = 26
  • In Excel, use the same MID() function to select these digits.
  • This part of the calculation’s Excel formula looks like this:
    

    =MID(C27,1,1)+MID(C27,3,1)+MID(C27,5,1)
    +MID(C27,7,1)+MID(C27,9,1)+MID(C27,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;

• Next, round the result up to the nearest 10. Our example’s result of rounding up from 92 is 100;
  • In Excel 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. It looks like this:
    

    =ROUNDUP(G4,-1)
    where G4 is the cell location of our un-rounded sum (92), and -1 basically just tells the function to round up to the nearest 10.

• The actual check digit is your rounded up number, minus the un-rounded sum; so 100 – 92 = 8.
• Our 13th and check digit for this fictitious barcode is 8.
• Lastly, automatically add your check digit to your 12 digit number with the Excel 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: 1234567890128

If you want minimal columns in your Excel 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.