Luhn check digit formula

[annnwy1]

Hey guys i need some help with Luhn check digit formula, i need to make formula for 13 and 14 digits for card number validation, i tried and i search online but it won't help... If someone knows or have the formula please help, it would be much appreciated!!

 

[lrobbo314]

Here is a UDF that should work.

Function Luhn(nstr As String)
Dim tmp As Integer
Dim Total As Integer

For i = 1 To Len(nstr)
    If i Mod 2 = 1 Then
        Total = Total + Int(Mid(nstr, i, 1))
    Else
        tmp = Int(Mid(nstr, i, 1)) * 2
        If tmp > 9 Then tmp = splitNum(CStr(tmp))
        Total = Total + tmp
    End If
Next i

Luhn = Total Mod 10 = 0

End Function

Function splitNum(n As String) As Integer
Dim Total As Integer

For i = 1 To Len(n)
    Total = Total + Int(Mid(n, i, 1))
Next i

splitNum = Total
End Function

 

[annnwy1]

Thanks , but i need formula like this one : =RIGHT(A1,1)=TEXT((10-MOD(SUMPRODUCT(LEFT(MID(LEFT(A1,LEN(A1)-1),ROW(OFFSET($A$1,,,LEN(A1)-1)),1)*(IF(ISEVEN(ROW(OFFSET($A$1,,,LEN(A1)-1))),2,1)),1)+IF(LEN(MID(LEFT(A1,LEN(A1)-1),ROW(OFFSET($A$1,,,LEN(A1)-1)),1)*(IF(ISEVEN(ROW(OFFSET($A$1,,,LEN(A1)-1))),2,1)))>1,RIGHT(MID(LEFT(A1,LEN(A1)-1),ROW(OFFSET($A$1,,,LEN(A1)-1)),1)*(IF(ISEVEN(ROW(OFFSET($A$1,,,LEN(A1)-1))),2,1)),1),0)),10)),"0")

but for 13 and 14 digits...

 

[Eric W]

This will validate numbers up to 16 digits:

Excel 2012

	A	B
79927398713

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]TRUE[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=MOD(MOD(SUM(IF(MID(TEXT(INT(A1/10),REPT("0",15)),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)="9",9,MOD(MID(TEXT(INT(A1/10),REPT("0",15)),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)*{2,1,2,1,2,1,2,1,2,1,2,1,2,1,2},9))),10)+RIGHT(A1),10)=0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

First off Eric, thanks for throwing out the formula solution, you saved me from banging my head against the wall trying to figure that out. I was messing around with a thousand MID(ROW(INDIRECT combos.

Also, what kind of black magic is that? How does that even work? Do you have a link or something that explains the logic of this formula?

 

[lrobbo314]

Just for kicks, here's a Power Query solution as well. @sandy666

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicate = Table.DuplicateColumn(Source, "Number", "Number - Copy"),
    Split = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Duplicate, {{"Number - Copy", type text}}, "en-US"), {{"Number - Copy", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Number - Copy"),
    ChangeType = Table.TransformColumnTypes(Split,{{"Number - Copy", Int64.Type}}),
    RowNum = Table.AddIndexColumn(ChangeType, "Index.1", 1, 1),
    Custom = Table.AddColumn(RowNum, "Custom", each Number.Mod([Index.1],2)),
    Custom1 = Table.AddColumn(Custom, "Custom.1", each if [Custom] = 0 then [#"Number - Copy"] * 2 else ""),
    Split2 = Table.SplitColumn(Table.TransformColumnTypes(Custom1, {{"Custom.1", type text}}, "en-US"), "Custom.1", Splitter.SplitTextByRepeatedLengths(1), {"Custom.1.1", "Custom.1.2"}),
    ChangeType1 = Table.TransformColumnTypes(Split2,{{"Custom.1.1", Int64.Type}, {"Custom.1.2", Int64.Type}}),
    Custom2 = Table.AddColumn(ChangeType1, "Custom2", each if [Custom.1.2] <> null then [Custom.1.1] + [Custom.1.2] else if [Custom] = 1 then [#"Number - Copy"] else [Custom.1.1]),
    Remove = Table.RemoveColumns(Custom2,{"Number - Copy", "Index.1", "Custom", "Custom.1.1", "Custom.1.2"}),
    Group = Table.Group(Remove, {"Number"}, {{"Total", each List.Sum([Custom2]), type number}}),
    Custom3 = Table.AddColumn(Group, "Check", each if Number.Mod([Total],10) = 0 then "Valid" else "Not Valid"),
    Remove1 = Table.RemoveColumns(Custom3,{"Total"})
in
    Remove1

 

[sandy666]

Just for kicks, here's a Power Query solution as well. @sandy666

 

[kytr11]

Hi,
is it possible to change this formula from 16 to go up to 20 digits?
Thank you in advance!

 

[Eric W]

Hmm, well, it's tricky to explain!

First, check out the Wikipedia article on the algorithm itself:

https://en.wikipedia.org/wiki/Luhn_algorithm

Next, consider this sheet:


Excel 2012

	A	B	C	D	E
79927398713	Digit	Digit * 1 result	Digit * 2 result

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=MOD(SUM(IF(MID(TEXT(INT(A1/10),REPT("0",15)),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)="9",9,MOD(MID(TEXT(INT(A1/10),REPT("0",15)),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)*{2,1,2,1,2,1,2,1,2,1,2,1,2,1,2},9)))+RIGHT(A1),10)=0[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=MOD(SUM(CHOOSE(MID(TEXT(INT(A1/10),REPT("0",15)),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)+{11,1,11,1,11,1,11,1,11,1,11,1,11,1,11},0,1,2,3,4,5,6,7,8,9,0,2,4,6,8,1,3,5,7,9))+RIGHT(A1),10)=0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



First, I noticed that I had an unnecessary MOD in the original formula, so I removed it. Next, I figured out a shorter and probably easier to explain formula in B2. So I'll explain that, and if you're interested, you can try to deconstruct the original.

OK, adding preceding zeros does not affect the end result, but it does make it easy to decide whether each individual digit should be multiplied by 1 or 2. So I first use INT(A1/10) to remove the last (check) digit, then I use TEXT to append as many preceding zeros are needed to make it 15 digits long. Next, I use MID(#,{1,2,3,...},1) to split the number into individual digits. This makes it an array formula, but when Excel sees the explicit array constant, you don't need to add the Control+Shift+Enter.

Now look at the table in C1:E11. This shows the values that are added to the overall sum for each digit, depending on whether we multiply by 1 or 2. Note that E7 is 1, because 5*2=10, and 1+0=1, according to the algorithm. So if we have this list hardcoded in the formula, all we need to do is figure out the position in the list. When we multiply by 1, we just need to add 1 to the digit's value to give us an index from 1 to 10. When we multiply by 2, we add 11 to the digit's value to give us an index from 11 to 20. You can see the array constant with those values. Then we SUM the values for each digit, add the check digit, take it MOD 10, and if that's 0, then the check digit is valid.

You might think it would be a bit easier to just make the number 16 digits long in the TEXT, so you don't have to add the check digit separately, but here's where we run up against the 15-digit precision limit in Excel.

In the first formula, I multiplied each digit by 1 or 2, then took that result MOD 9 to get the result from table C:E. That works fine for every digit except 9, which is why I had to handle that separately.

Hope this makes some sense!

 

[Eric W]

Welcome to the forum.

Yes, it can go up to 20 digits, but like I mentioned in my last post, we run against Excel's 15-digit precision limit. To get around that, you'd need to format the cell as Text, not General. Then we'd also need to eliminate the TEXT from the formula. You'd end up with something like this:

=MOD(SUM(CHOOSE(MID(REPT("0",20-LEN(A1))&A1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20},1)+{11,1,11,1,11,1,11,1,11,1,11,1,11,1,11,1,11,1,11,1},0,1,2,3,4,5,6,7,8,9,0,2,4,6,8,1,3,5,7,9)),10)=0