Hello Mrjames,
Here are 2 macros that should help you out. One will verify if a
checksum sequence is correct, and the other will generate a checksum
digit for a sequence, and if the sequence is valid, no checksum digit
needed, it will return a -1.
Using the number you provided we will get the following result:
Cell A1 formula: =IsLuhnChecksumOK("49015420323751")
Returns TRUE. This sum of the digits equals 50, not 52.
How the Luhn algorithm functions:
1) Starting with the second to last digit, multiply every other digit
by 2.
2) If the product is greater than 10 then subtract 9 from the product.
This produces the same result as adding the digits of number greater
than 9. Sum these values.
3) Sum all the unmultiplied digits and add the sum from step 2. The
result is valid if it is an even mutliple of 10.
4 9 0 1 5 4 2 0 3 2 3 7 5 1
Step 1 - double the digits
5(2) 3(2) 3(2) 2(2) 5(2) 0(2) 4(2)
Step 2 - sum and adjust the doubled digits
(10 - 9) + 6 + 6 + 4 + (10 - 9) + 0 + 8 = 26
Step 3 - sum the digits not doubled and the doubled digits. Test is
total is evenly divided by 10
1 + 7 + 2 + 0 + 4 + 1 + 9 = 24
24 + 26 = 50
10 Mod 50 = 0
Code:
--------------------
Public Function IsLuhnChecksumOK(Number_String As String) As Boolean
Dim Digit As Integer
Dim i As Integer
Dim N As Integer
Dim Result As Integer
Dim SumDigits As Integer
Dim nDigits As Integer
SumDigits = 0
nDigits = Len(Number_String)
For i = nDigits To 1 Step -1
Digit = CInt(Mid(Number_String, i, 1))
N = N + 1
If N Mod 2 = 0 Then Digit = Digit * 2
If Digit 9 Then Digit = Digit - 9
SumDigits = SumDigits + Digit
Next i
Result = SumDigits Mod 10
If Result = 0 Then
IsLuhnChecksumOK = True
End If
End Function
Public Function GetLuhnCheckDigit(Number_To_Check As String)
Dim J As Integer
Dim X
X = IsLuhnChecksumOK(Number_To_Check)
If X = False Then
For J = 0 To 9
X = IsLuhnChecksumOK(Number_To_Check & J)
If X = True Then
'Check digit found
GetLuhnCheckDigit = J
Exit Function
End If
Next J
Else
'No check digit needed returns -1
GetLuhnCheckDigit = -1
End If
End Function
--------------------
If you have any further questions, you can emial me at
.
Sincerely,
Leith Ross
--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=482390