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: In Excel / Google Sheets use the MID() function to select the specific digits.Starting with,and including, the 2nd digit, take every other digit in your 12 digit number, add them all together and times by 3.For ease of demonstration, I’ll use 123456789012 as my 12 digit example number (in hindsight this just made things confusing, but nevermind). I guess this is to verify that the numbers scanned properly.

In practice, the 13th digit determines whether or not your other 12 numbers in the barcode are correct. Finicky-but-not-complicated Check Digit Formula

You can have 1,000,000 unique barcodes/products with this numbering system per day.Ģ.

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.

The next 6 digits are just sequential, and Excel will sort that out, so I added 000001.

The date made up the 1st 6 digits: 100810.

You need unique strings of 12 numbersįirst, 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. Excel Formula for that 13 -Digit Barcode Check Digit 1. 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. For one, OpenBravoPOS won’t even generate the barcode if the check digit isn’t right. 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. 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. 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. Some have, some don’t, so I created barcodes for all the products. To make products scanable, they need barcodes. Then, with a little preparation, you can export the data from Excel and import it in to your database. 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.Īn easy workaround is to set up the database structure in Excel. What isn’t so exciting is the process of stock taking, and click-click-clicking your way a thousand times around the OpenBravoPOS interface. I’ve been tinkering with OpenBravoPOS for a while – it’s an exciting learning curve.

It works in Google Sheets too!Ĭlick 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. Here’s the Excel formula I constructed to generate a 13-digit barcode check digit. 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.

Barcode generator excel 2010