Does anyone know how to generate a unique card number based on a
previously existing number? I'm trying to create a 16 digit Check
number by only given the first 15 digits. I've been to this site:
http://www.beachnet.com/~hstiles/cardtype.html to use the Excel spread
sheet file and it doesn't give me the info I need. Any help is
welcomed.

Thanks,
John


--
Union70

Based on the instruction for calculating a MOD 10 check
digit at this website,

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

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)

HTH
Jason
Atlanta, GA

-----Original Message-----

Does anyone know how to generate a unique card number
based on a
previously existing number? I'm trying to create a 16
digit Check
number by only given the first 15 digits. I've been to
this site:
http://www.beachnet.com/~hstiles/cardtype.html to use
the Excel spread
sheet file and it doesn't give me the info I need. Any
help is
welcomed.

Thanks,
John


--
Union70
.

"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","0123 456789"))-1),10)

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)

"Myrna Larson" wrote...
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.
....

Me, I prefer a more pointer-like approach, and I prefer to keep error
checking outside loops.


Function LUHN(ByVal ds As String, Optional dw As Boolean = False) As Long
Const EVENDIGITS As String = "0516273849"
Const ODDDIGITS As String = "0123456789"

Dim k As Long, n As Long
Dim ed As String, od As String

ds = Application.WorksheetFunction.Substitute(ds, " ", "")
ds = Application.WorksheetFunction.Substitute(ds, "-", "")

If ds Like "*[!0-9]*" Then
LUHN = -1
Exit Function
End If

n = Len(ds)
LUHN = -n

If dw Then
ed = EVENDIGITS
od = ODDDIGITS
Else
ed = ODDDIGITS
od = EVENDIGITS
End If

For k = n To 2 Step -2
LUHN = LUHN + InStr(od, Mid(ds, k, 1)) + InStr(ed, Mid(ds, k - 1,
1))
Next k

If k = 1 Then LUHN = LUHN + InStr(od, Mid(ds, k, 1))

LUHN = (10 - LUHN Mod 10) Mod 10
End Function