In case anyone is interested in a VBA solution, here's a routine. The input
can include digits plus dashes and spaces (which are ignored). If it includes
any other characters, it returns -1 to indicate an error.

It's "dual purpose". If you call it with the 2nd argument = False or omitted,
it returns the check digit. With 15 digits in A1, the formula to get the check
digit is =CheckDigit(A1)

If the 2nd argument is True, that means the right-most digit IS the check
digit; in this case, the returned value should be 0. If it isn't, the you
don't have a valid card number. The formula

=CheckDigit(A1,True)=0

can be used to check a card number.

Function CheckDigit(CardNum As String, _
Optional HasCheckDigit As Boolean = False) As Long
'ignores space and dash, other non-numeric characters give error
Dim Bytes() As Byte
Dim C As Long
Dim Dbl As Boolean
Dim Digit As Long
Dim Sum As Long

Bytes() = CardNum
Dbl = HasCheckDigit 'toggles before each digit
Sum = 0

For C = UBound(Bytes) - 1 To 0 Step -2
Digit = Bytes(C)

Select Case Digit
Case 48 To 57 '0 to 9
Digit = Digit - 48
Dbl = Not Dbl
If Dbl Then
Digit = Digit + Digit
If Digit 9 Then Digit = Digit - 9
End If
Sum = Sum + Digit
If Sum 9 Then Sum = Sum - 10

Case 32, 45 'ignore space or dash

Case Else 'error with anything else
CheckDigit = -1
Exit Function
End Select
Next C

If Sum Then Sum = 10 - Sum
CheckDigit = Sum

End Function 'CheckDigit


On Mon, 7 Mar 2005 20:21:32 -0800, "Harlan Grove" wrote:

"Jason Morin" wrote...
Based on the instruction for calculating a MOD 10 check
digit at this website,

http://www.morovia.com/education/utility/upc-ean.asp

None of the entries on this web site deal with 16th checksum digit based on
the first 15 digits.

you could use this formula:

=10-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&ROUNDUP(LE N
(A1)/2,0)))*2-1,1)+0)*IF(ISODD(LEN(A1)),3,1)+SUMPRODUCT
(MID(A1,ROW(INDIRECT("1:"&INT(LEN(A1)/2)))*2,1)+0)*IF
(ISODD(LEN(A1)),1,3),10)
...

And now you're completely screwed up. On *your* web site all the odd digits
are multiplied by 3 (other than ISBN, which is really weird), but the OP
provided a link to a web site describing LUHN check digits, in which
alternate digits are multiplied by *TWO* and the resulting digits summed.
Were you just too lazy to check the site for which the OP provided a link,
or did you fail to understand it?

Anyway, LUHN checksums have been handled before in this ng, but I can't find
a formula Here's one that doesn't need to be an array formula.

=10-MOD(SUMPRODUCT((MID(B1,ROW(INDIRECT("1:15")),1)"0 ")
*(1+MOD(MID(B1,ROW(INDIRECT("1:15")),1)
*(1+MOD(ROW(INDIRECT("1:15")),2))-1,
10-MOD(ROW(INDIRECT("1:15")),2)))),10)

Here's one that does have to be an array formula.

=10-MOD(SUM(FIND(MID(B1,ROW(INDIRECT("1:15")),1),
IF(MOD(ROW(INDIRECT("1:15")),2),"0516273849","012 3456789"))-1),10)