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

Hi M. Jack,

To modify the formula to calculate the 13th digit (check digit) for a 13 digit UPC A code, you can use the following formula:

Here's a breakdown of the formula:

1. A1& - This concatenates the original 12 digits with the calculated 13th digit.
2. SUMPRODUCT - This function multiplies each digit by a corresponding weight and then sums the products.
3. MID - This function extracts a single digit from the original 12 digits.
4. IF - This function checks if the row number is even or odd and returns the appropriate weight.
5. MOD - This function checks if the row number is even or odd and returns the appropriate weight.
6. INT - This function rounds down the row number to the nearest integer.
7. 10-MOD - This function subtracts the sum from the nearest higher multiple of 10 and returns the difference.
8. MOD - This function calculates the remainder when the sum is divided by 10.
9. 10-MOD - This function subtracts the remainder from 10 and returns the difference.

Hope this helps!

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"
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 O wrote:
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.

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 to pay for a soluation!

On Tuesday, April 9, 2013 10:21:02 PM UTC+23, wrote:
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?

Not following your question: are you trying to add 6 zeroes to the front of a 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!

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?