Create barcode in single cell using product code

[Paulo19]

I have a cell called "Product Code" and I want to use this to generate a barcode in a cell called "Barcode" without using any other cells.

So, the "Barcode" cell would contain the company prefix, product code and the check digit e.g. "5053381" "18220" "4" (final format should be 5053381182204).

The algorithm to generate the check digit is (sum of all odd numbers x3) + (sum of all even numbers) modulo 10, which is "4" for this barcode.

The formula would be something like: 5053381 (concatenate) "Product Code" (concatenate) "check digit algorithm".

Thanks

[Tinbendr]

Copy this to a Module code page.

Please Login or Register  to view this content.

(C10 was where the product code was on my sheet. Change as required.)
Then, use the formula in the Barcode column.

Please Login or Register  to view this content.

[Paulo19]

Perfect, thank you Tinbendr.

[Paulo19]

I gave you the wrong algorithm to generate Check Digits. Any chance you can amend your code using the correct algorithm below?

Locate the 12th digit in the barcode.
For example, if your EAN-13 was 5054492 22115 x, the 12th digit is the No. 5.

Starting with this 12th digit, move from right to left across the code adding every second digit to it. Using our example code of 5054492 22115 x, this means starting with the No. 5 and adding to it the numbers 1, 2, 9, 4 and 0, giving a total of 21.

Multiply the sum obtained in Step 2 by 3. Using our example above, this means the multiplying of 21 by 3, giving a total of 63.

Locate the 11th digit in the code. Using our example code of 5054492 22115 x, this would be the No. 1.

Starting with the 11th digit, move from right to left across the code adding every second digit to it. Using our example of 5054492 22115 x, this means starting with the No. 1 and adding to it 2, 2, 4, 5 and 5, giving a total of 19.

Add the results from Step 3 and Step 5. In our example this means adding 63 and 19, giving a total of 82.

Round the result of Step 6 up to the nearest multiple of 10. In our example, this means rounding 82 up to 90.

Subtract the result of Step 7 from the result of Step 6. In our example, this is 90-82, giving us a difference of 8. This 8 should be the 13th number in the EAN-13, otherwise known as the check digit.

Result: 5054492 22115 8

[Tinbendr]

Please Login or Register  to view this content.

[Paulo19]

Awesome, thanks Tinbendr.

I also put up a new post for this. Here's another solution if you're interested.

http://www.excelforum.com/showthread...t=#post4450882

[Paulo19]

Tinbendr,

The new code seems to produce strange results where the total for Odd and Even numbers exceed 100... I think.

eg. "5054492(company prefix) 64056(product code)" results in "505449264056-80", it should be "5054492640560".

Any ideas?

Here's the code i'm using:

="5054492" & TEXT(A1,"#0") & CheckDigit2("5054492" & A1)

Function CheckDigit2(Target As String) As String
Dim A As Long
Dim Temp, Temp2
Dim CkOdd As Long
Dim CkEven As Long
Dim RightNum As String

For A = Len(Target) To 1 Step -1
Select Case A Mod 2
Case 0
CkEven = CkEven + Val(Mid(Target, A, 1))
Case 1
CkOdd = CkOdd + Val(Mid(Target, A, 1))
End Select
Next
Temp = CkOdd + (CkEven * 3)
Temp2 = Temp - (Temp Mod 10) + 10
RightNum = Temp2 - Temp
CheckDigit2 = RightNum
End Function





Any chance you can try this too? It seems to work better.

Example barcode number: 501234576421

Step 1: add together all alternate numbers starting from the right
5 0 1 2 3 4 5 7 6 4 2 1
0 + 2 + 4 + 7 + 4 + 1 = 18

Step 2: multiply the answer by 3
18 x 3 = 54

Step 3: now add together the remaining numbers
5 0 1 2 3 4 5 7 6 4 2 1
5 + 1 + 3 + 5 + 6 + 2 = 22

Step 4: add step 2 and 3 together
54 + 22 = 76

Step 5: the difference between step 4 and the next 10th number:
76 + 4 = 80
Check digit = 4



Thanks

[Tinbendr]

Then

Please Login or Register  to view this content.