Luhn check digit formula

[bhmcpfs]

I'm charged with coming up with a formula using the "Luhn check digit formula" for a 10-digit number, calculated as follows: (The check digit is the last number.)

1. Double the value of alternate digits beginning with the first digit.
2. Add 24 to the individual digits of the products resulting from step 1 to the unaffected digits from the original number.
3. Subtract the total obtained in step 2 from the next higher number ending in zero. This is the check digit. If the total obtained in step 2 is a number ending in zero, the check digit is zero.

Example: Number is 1234567893

1 2 3 4 5 6 7 8 9 3
2 6 10 14 18 (step 1)
24+2+2+6+4+1+0+6+1+4+8+1+8=67 (step 2)
70-67=3 (step 3)

I'm not getting anywhere with my attempts, and the formula is getting uglyier (is that a word?) with each attempt. Any help from the board would be much appreciated! Thanks in advance, and sorry for the long post...

 

[Richard Schollar]

Hi there

This thread here:

http://www.mrexcel.com/board2/viewtopic.php?t=270865&start=0&postdays=0&postorder=asc&highlight=luhn

has lots and lots on Luhn, including a formula you can use to check a card number (with an even number of digits) complies.

 

[bhmcpfs]

Thanks. I tried various luhn check digit searches and didn't have any luck...

Seems like the long formulas add a check digit to my #'s. But my numbers already have the check digit added - I need to verify their accuracy. I've been trying to use something involving sum/len and am stuck at step 2 (didn't get very far, did I?)

 

[ravishankar]

Hi
Insert your test number in cell A1 and Paste the following codes in the macro window (Alt F8)

For a = 1 To Len(Cells(1, 1)) Step 2
Cells(a + 1, 2) = Mid(Cells(1, 1), a, 1) * 2
Cells(a + 2, 2) = Mid(Cells(1, 1), a + 1, 1)
Next a
Cells(1, 3) = 24
For b = 2 To Len(Cells(1, 1))
If Len(Cells(b, 2)) > 1 Then
C = Mid(Cells(b, 2), 1, 1)
d = Mid(Cells(b, 2), 2, 1)
Cells(1, 3) = Cells(1, 3) + C + d
Else
Cells(1, 3) = Cells(1, 3) + Cells(b, 2)
End If
Next b
Cells(2, 3) = "=round(C1,-1)"
Cells(3, 3) = "=C2-C1"

run the macro. Cell C3 gives answer as 3.
Ravi

 

[bhmcpfs]

forgot to add that I need a formula, some workplaces won't allow macros. Very sorry I didn't post this originally...

 

[Richard Schollar]

Hi

This is the formula I was thinking of:

Excel Workbook
	A	B
1	Credit Card Number	Luhn Check
2	6334505098134250	51
3	1234567897	50
4	8945632451	45
Sheet3

 

[bhmcpfs]

Wow!... formula is WAY more involved than I can figure out. But way shorter than mine...

Formula seems to verify a 2-digit check number, correct? Mine needs to validate a single digit...

Two samples:

1104806504
1356310189

check digits are 4 and 9 respectively (the 10th digit). How should your formula be modified to validate?

 

[Richard Schollar]

Hi

The following illustrates the calculation steps. It doesn't need to be modified - it will work with even numbered card numbers. You will probably want the card numbers enetered as text in the cell though (ie '123456 etc rather than 1234567 etc - note the single quote).

Excel Workbook
	A	B
1	Credit Card Number	Luhn Check
2	1234567897	50
3		#=SUM(--MID(TEXT(MID(A2,{1;2;3;4;5;6;7;8;9;10},1)*(MOD({1;2;3;4;5;6;7;8;9;10},2)+1),"00"),{1,2},1))
4		#=SUM(--MID(TEXT({"1";"2";"3";"4";"5";"6";"7";"8";"9";"7"}*({1;0;1;0;1;0;1;0;1;0}+1),"00"),{1,2},1))
5		#=SUM(--MID(TEXT({"1";"2";"3";"4";"5";"6";"7";"8";"9";"7"}*{2;1;2;1;2;1;2;1;2;1},"00"),{1,2},1))
6		#=SUM(--MID({"02";"02";"06";"04";"10";"06";"14";"08";"18";"07"},{1,2},1))
7		#=SUM(--{"0","2";"0","2";"0","6";"0","4";"1","0";"0","6";"1","4";"0","8";"1","8";"0","7"})
8		#=SUM({0,2;0,2;0,6;0,4;1,0;0,6;1,4;0,8;1,8;0,7})
9		50
Sheet3

 

[Richard Schollar]

Incidentally, both those numbers you gave fail:

Excel Workbook
	A	B
11	1104806504	26
12	1356310189	36
Sheet3

 

[bhmcpfs]

Yeah, I noticed that too (that the two examples fail). I think I see my error... The last digit is the check digit, and should not be used in the calculation. So 1104806504 should yield 22, but I have to add a constant 24, which gives 46. 50-46=4. Check. 1356310189 should yield 27, plus 24 = 51. 60-51=9. Check again. How should your formula be altered to fix? Thanks a bunch for all your help!