Forum Home
Press F1
 
Thread ID: 34565 2003-06-17 07:46:00 Barcodes John Robb (825) Press F1
Post ID Timestamp Content User
153082 2003-06-17 07:46:00 Does anyone have an Excel formula for calculating the check digit on an EAN 13 Bar code?
I have tried to write one but it is quite complex so I thought that maybe someone has already done it.
Any suggestions would be appreciated.
Thanks 		John Robb (825)
153083 2003-06-17 08:17:00 Please what is a check digit on an EAN 13 Bar code and I may be able to help. mikebartnz (21)
153084 2003-06-17 08:39:00 putting "check digit on an EAN 13 Bar code" into google gets 2850 results in 0.17 seconds.

Very interesting reading. 		E.ric (351)
153085 2003-06-17 08:40:00 A 13 Digit Barcode number has 12 digits which are the actual part number and the 13th digit is a calculated number the barcode reader recalculates each time the barcode is read to verify all the other digits are read correctly.
The formula is as follows.
Step 1 Start with the right most digit and add all the alternative numbers
Step 2 Multiply the result by 3
Step 3 Starting with the second digit from the right, add all the remaining digits
Step 4 Add the results of steps 2 & 3
Step 5 Add a number to round the total to the next multiple of 10.
The check digit is this last number.
eg. a barcode number of 942001681001 has a check digit of 0
1. add 1+0+8+1+0+4 = 14
2. 14 x 3 = 42
3. add 0+1+6+0+2+9 = 18
4. add 18 + 42 = 60
5. no extranumber is needed to reach a multiple of 10 so the check digit is 0.

I would ideally like to have a formula that could be in one cell and it would dissect a 12 digit number and calculate the check digit. This maybe too tough and I may have to spread all the digits across 12 cells to make it work.
Any help would be appreciated 		John Robb (825)
153086 2003-06-17 08:52:00 Once I considered there should be a computer program, you enter the bar code of food you eat, along with how much you eat, like two slices of bread, one tin of . . . . . ?

and it works out if you are eating a balanced meal and if you are short of some food, but it never got past that thinking stage .

not too forget the belt idea were the holes have a "height" near each hole if you are too short for the height hole you use then you need to loose weight . 		E.ric (351)
153087 2003-06-17 09:38:00 Try this formula for a 12 digit number (or text number) in A1


=IF(VALUE(RIGHT((VALUE(MID(A1,12,1))+VALUE(MID(A1, 10,1))+VALUE(MID(A1,8,1))+VALUE(MID(A1,6,1))+VALUE (MID(A1,4,1))+VALUE(MID(A1,2,1)))*3+VALUE(MID(A1,1 1,1))+VALUE(MID(A1,9,1))+VALUE(MID(A1,7,1))+VALUE( MID(A1,5,1))+VALUE(MID(A1,3,1))+VALUE(MID(A1,1,1)) ,1))=0,0,10-(VALUE(RIGHT((VALUE(MID(A1,12,1))+VALUE(MID(A1,10, 1))+VALUE(MID(A1,8,1))+VALUE(MID(A1,6,1))+VALUE(MI D(A1,4,1))+VALUE(MID(A1,2,1)))*3+VALUE(MID(A1,11,1 ))+VALUE(MID(A1,9,1))+VALUE(MID(A1,7,1))+VALUE(MID (A1,5,1))+VALUE(MID(A1,3,1))+VALUE(MID(A1,1,1))))) )


HTH 		Russell D (18)
153088 2003-06-17 11:42:00 Here is a macro that will do what you want. To use as is just place the cursor in the cell to the right of the number. You could alter it to iterate through the numbers.

Sub GetNum()
A = 0
B = 0
TheNum$ = Str(ActiveCell.Offset(0, -1))
For Counter = 0 To 5
A = A + FormatNumber(Mid(TheNum$, Len(TheNum$) - (Counter * 2), 1), 0)
Next Counter
A = A * 3
For Counter = 0 To 5
B = B + FormatNumber(Mid(TheNum$, Len(TheNum$) - ((Counter * 2) + 1), 1), 0)
Next Counter
C = A + B
D$ = Mid(Str(C), Len(Str(C)), 1)
If D$ <> "0" Then
E = 10 - FormatNumber(D$, 0)
Else
E = 0
End If
ActiveCell.Value = E
End Sub 		mikebartnz (21)
153089 2003-06-18 07:45:00 Many thanks Russell - it works a treat. I doubt I would have ever got there on my own.
Much appreciated
John 		John Robb (825)
153090 2003-06-18 07:53:00 Thanks Mike, I have used Russell's formula as that suits my needs a little better than a macro. However I have filed your macro for future use.
Again thanks for your help
John 		John Robb (825)
1