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