How can I Calculate Check Digit for UPC A - the 13th warrior

  • Thread starter Thread starter AIRJACK
  • Start date Start date
A

AIRJACK

I found this great formula (below) for calculating the 12th (check
digit) for a 12 digit upc code and then yielding the entire code
including 12th digit. Does anybody know how to modify or alter this
formula to calculate the 13th digit (check digit) for a 13 digit UPC A
code and yield the entire code including the 13th digit?

=A1&MOD(10-MOD(SUMPRODUCT(MID(A1,ROW($1:$11),1)*
(1+2*MOD(ROW($1:$11),2))),10),10)


The long way is:
Stating as example the following code: 739007812345

Start by adding every other figure from the right and then multiply by
3.
5 + 3 + 1 + 7 + 0 + 3 = 19 * 3 = 57
After this add the rest of the figures:
4 + 2 + 8 + 0 + 9 + 7 = 30
Add the above results 57 + 30 = 87
Next reduce this amout 87 from the closest higher ten (90) and you
receive 90 - 87 = 3
3 is the check digit.

Thanks,

M. Jack
 
This is not a formula, but it is an answer.
It uses vba code and will calculate the check digit for any
length number (Using Your Rules).
Paste the following code into a standard module (Alt + F11).
Then enter in a cell "=checkdigit(739007812345,12)" - without the quotes.
The number after the comma is the number of characters to
the left of the comma.
The formula above returns 7390078123453 in the cell.
If your Upc number is in cell A1 then use "=checkdigit(A1,12)"

"=checkdigit(12345,5)" returns 123457 in the cell.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Function CheckDigit(ByRef dblChars As Double, _
ByRef HowMany As Long) As String
Dim N As Long
Dim lngLen As Long
Dim lngSumR As Long
Dim lngSumL As Long
Dim lngTotal As Long
Dim strTemp As String

strTemp = CStr(dblChars)
lngLen = Len(strTemp)
'Confirm that entry is correct.
If lngLen <> HowMany Then
CheckDigit = "Incorrect Entry"
Exit Function
End If

'Add first set of numbers starting from right.
For N = lngLen To 1 Step -2
lngSumR = lngSumR + Mid(strTemp, N, 1)
Next
lngSumR = lngSumR * 3

'Add second set of numbers.
'starting 2nd character from right.
For N = (lngLen - 1) To 1 Step -2
lngSumL = lngSumL + Mid(strTemp, N, 1)
Next
N = lngSumR + lngSumL

'Round up
lngTotal = (N Mod 10)
lngTotal = 10 - lngTotal + N

CheckDigit = strTemp & (lngTotal - N)
End Function
'----------------


"AIRJACK" <[email protected]>
wrote in message
I found this great formula (below) for calculating the 12th (check
digit) for a 12 digit upc code and then yielding the entire code
including 12th digit. Does anybody know how to modify or alter this
formula to calculate the 13th digit (check digit) for a 13 digit UPC A
code and yield the entire code including the 13th digit?

=A1&MOD(10-MOD(SUMPRODUCT(MID(A1,ROW($1:$11),1)*
(1+2*MOD(ROW($1:$11),2))),10),10)
The long way is:
Stating as example the following code: 739007812345
Start by adding every other figure from the right and then multiply by
3.
5 + 3 + 1 + 7 + 0 + 3 = 19 * 3 = 57
After this add the rest of the figures:
4 + 2 + 8 + 0 + 9 + 7 = 30
Add the above results 57 + 30 = 87
Next reduce this amout 87 from the closest higher ten (90) and you
receive 90 - 87 = 3
3 is the check digit.
Thanks,
M. Jack
 
Here's my submission:
=A1&MOD(10-MOD(SUMPRODUCT(MID(A1,ROW($1:$12),1)*(1+2*ABS(MOD(ROW($1:$12),2)-1))),10),10)

My hat is off to whoever provided the original formula: I learned a lot
today. Very clever!

Strongly recommended to test against samples that are known to be
correct.
 
Dave said:
Here's my submission:
=A1&MOD(10-MOD(SUMPRODUCT(MID(A1,ROW($1:$12),1)*(1+2*ABS(MOD(ROW($1:$12),2)-1))),10),10)

My hat is off to whoever provided the original formula: I learned a lot
today. Very clever!

Strongly recommended to test against samples that are known to be
correct.
Click to expand...

Hi Dave,

It works perfectly! Thanks!

M. Jack
 
These formula's are great! One of the biggest problems I'm having is calculating the check digit for UPC-E 7 digits and UPC-E with leading 0's with would be 6 digits. Any recommendations?

Also, and this would be AMAZING, my biggest issue is check UPC-A's and UPC-E's for check digits to see if they are valid UPCs. I work with 100,000+ UPC items a day and it's really hard processing them because I have to determine which items contain check digits and which ones do not. I am willing topay for a soluation!
 
These formula's are great! One of the biggest problems I'm having is calculating the check digit for UPC-E 7 digits and UPC-E with leading 0's with would be 6 digits. Any recommendations?
Click to expand...

Not following your question: are you trying to add 6 zeroes to the front ofa 7 digit string?
Also, and this would be AMAZING, my biggest issue is check UPC-A's and UPC-E's for check digits to see if they are valid UPCs. I work with 100,000+ UPC items a day and it's really hard processing them because I have to determine which items contain check digits and which ones do not. I am willing to pay for a soluation!
Click to expand...

In the OP's question he had a 12-digit number that required a check digit. Not following this part of the question either: if the number has a check digit it will be a certain length, and if it does not have a check it will be one character short, correct?
 
Back
Top