Excel formula to test if 15 or 16 digit number is a valid credit card and return true / false (Luhn's algorithm)

Here is one formula to calculate the result, in latest version of Excel it works without anything special, in earlier version you need to enter it as an array formula (press CTRL+SHIFT+ENTER after typing it).

formula in C2:
=SUM(INT(MID(REPT("0",20-LEN(B2))&B2,ROW($1:$20),1)*(MOD(ROW($1:$20),2)+1)/10)+MOD(MID(REPT("0",20-LEN(B2))&B2,ROW($1:$20),1)*(MOD(ROW($1:$20),2)+1),10))

formula in D2 (final result):
=MOD(SUM(INT(MID(REPT("0",20-LEN(B2))&B2,ROW($1:$20),1)*(MOD(ROW($1:$20),2)+1)/10)+MOD(MID(REPT("0",20-LEN(B2))&B2,ROW($1:$20),1)*(MOD(ROW($1:$20),2)+1),10)),10)=0

How it works:

• REPT("0",20-LEN(B2))&B2 generates the 20 number long number with leading 0s
• ROW($1:$20) generates list of numbers 1 to 20 (that part should not be changed!)
• MOD(ROW($1:$20),2)+1 generates alternating numbers 1 & 2
• IF(D6<10,D6,INT(D6/10)+MOD(D6,10)) from original formula: for number x: 0-9, x = int(x/10) + mod(x,10) is always true, so this test can be excluded