?

Log in

No account? Create an account
Excel - AMAZINGY EASY EAN Check digit calculator. - Sarah's Blog [entries|archive|friends|userinfo]
Sarah

[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

Excel - AMAZINGY EASY EAN Check digit calculator. [Jul. 3rd, 2011|08:32 am]
Sarah
Are you sick of visiting sites like GS1 to create your check digits?
http://www.gs1.org/barcodes/support/check_digit_calculator

Perhaps you've then looked how to do them yourself:

The manual way to calculate the EAN check digit.

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
........... o In my example: (2 + 4 + 6 + 8 + 0 + 2) * 3 = 66
........... o 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.
........... o 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?
........... o In my example: 1 + 3 + 5 + 7 + 9 + 1 = 26
........... o In Excel, use the same MID() function to select these digits.
........... o 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);
........... o 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;
........... o 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.


It's complicated - and doubly so if you're trying it on a Monday morning!
So, I've converted this to an Excel function. The advantages of this over using a formula in the workbooks themselves are:

1: It cuts down on duplicated cells - there's no need for a "pre-check-digit" row, and a "post-check-digit-row".

2: It works on any length barcodes - I'd imagine it would be very difficult to do the same in a cell formula!

3: It can be applied to a huge range of cells at once!

Here's the VBA script, which turns out to be very small!:
Function calculateCheckDigit(value)
    lenval = Len(value)
    factor = 3
    Sum = 0
    For Index = lenval To 1 Step -1
        Sum = Sum + (CInt(Mid(value, Index, 1)) * factor)
        factor = 4 - factor
    Next
    calculateCheckDigit = ((1000 - Sum) Mod 10)
End Function

Sub addCheckDigit()
    If TypeName(Selection) <> "Range" Then Exit Sub
    For Each i In Selection
        currentValue = i.value
        i.value = currentValue & calculateCheckDigit(currentValue)
    Next
End Sub
addCheckDigit is called first to "scan through" all of the selected cells on the current worksheet.
The core of the algorithm is calculateCheckDigit. This is passed a string (in case the number starts with a zero), and applies the algorithm you've seen in the textbox above - it then returns to the calling function the original string with the check digit appended. If the function finds a number, it will calculate a check digit for you - if the cell's blank - it will leave it alone.

You can download an example spreadsheet showing how to use it here:
http://untamed.co.uk/miscFolder/BarCodeCalculator.xls

linkReply

Comments:
[User Picture]From: sarahs_muse
2017-10-27 03:03 am (UTC)

Re: This is awesome!

Thank you!
(Reply) (Parent) (Thread)