Luhn Test (Credit and Debit Card Number Validator - Worldwide) Within Excel - Build-In Algorithm ?

[puppy]

Hi All !

For validating credit and debit card numbers, regardless of their type and country where they were issued, I am wondering if Excel Pro 2013 (windows 10) has any feature to validate the card number located in few houndreds, few thousands or few tens of thousands of cells, all in the same column? Each cell containing different card number which is going to be validated. If yes, how can I use this feature and where to access it?

Answer would be appreciated.

 

[oldbrewer]

are you going to use the WEB to validate the numbers or are you going to define the rules

eg UK mastercard xxxx xxxx xxxx xxxx

 

[puppy]

There are no rules to be defined in EXISTING Luhn test. I just have several card numbers and want to validate them whether or not card number is valid. The main reason for such need is because of the following free online tool:

Mobilefish.com - Online credit card number checker

It can only validate max 100 at the same time.

 

[Jon von der Heyden]

Excel doesn't specifically have a tool for Luhn, but you can do the math using formulas. E.g.:

Excel 2013
	A	B	C	D	E	F	G
1	Number	Key	Check Num	Reverse Key	Mult 2 less 9	Mod 10	Result
2	4556737586899855	455673758689985	5	589986857376554	85	5	TRUE
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,15)
C2	=RIGHT(A2,1)
F2	=TEXT(MOD(E2,10),0)
G2	=F2=C2
D2	{=TEXT(SUM(MID(B2,reverse_sequence,1)*10^(reverse_sequence-1)),0)}
E2	{=TEXT(SUM(IF(MID(D2,sequence,1)*multiplier>9,MID(D2,sequence,1)*multiplier-9,MID(D2,sequence,1)*multiplier)),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
multiplier	={2,1,2,1,2,1,2,1,2,1,2,1,2,1,2}
reverse_sequence	={15,14,13,12,11,10,9,8,7,6,5,4,3,2,1}
sequence	={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}

 

[Macropod]

You might be interested in my reply at: http://answers.microsoft.com/en-us/...control/9b8b3bd1-6852-4fba-aa9b-c1766f1eba28/
The solution there works for Visa, Mastercard, Amex & Diners Club cards. Although written for use with content controls in Word, the code could be adapted for Excel.

 

[puppy]

Jon: I don't have such sections of a card numbers. In B column I only have few thousands or few tens of thousands of card numbers I would like to verify whether or not number is correct. Thats all.

Macropod: I wish i knew what to do with this code

 

[Macropod]

Add the following function to an ordinary code module in your workbook:

Function ValidateLuhn(ByVal oCell As Range) As Boolean
Dim i As Long, j As Long, k As Long, StrTmp As String
ValidateLuhn = False
StrTmp = Replace(Replace(oCell.Value, " ", ""), "-", "")
If IsNumeric(StrTmp) Then
  Select Case Len(StrTmp)
    Case 14: If Left(StrTmp, 1) <> 3 Then Exit Function 'Diners Club
    Case 15 'Amex
      If Left(StrTmp, 1) <> 3 Then Exit Function
      StrTmp = "0" & StrTmp
    Case 16 'Visa = 4, MasterCard = 5, Discover = 6
      If (Left(StrTmp, 1) < 4) Or (Left(StrTmp, 1) > 6) Then Exit Function
    Case Else: Exit Function
  End Select
  For i = 1 To Len(StrTmp)
    j = Mid(StrTmp, i, 1) * (i Mod 2 + 1)
    k = k + Int(j / 10) + j Mod 10
  Next
  If k Mod 10 = 0 Then ValidateLuhn = True
End If
End Function

To use it, simply input the formula as:
=ValidateLuhn(A1)
in any cell, where 'A1' is the address of the cell you want to test. The function should return TRUE for valid card #s; FALSE otherwise.

 

[Jon von der Heyden]

Jon: I don't have such sections of a card numbers. In B column I only have few thousands or few tens of thousands of card numbers I would like to verify whether or not number is correct. Thats all.

Macropod: I wish i knew what to do with this code

The numbers go in column A. The formulas in columns B:G are copied down to the end of the range (i.e. adjacent to all numbers). The final result is displayed in column G.