# Off Topic > Tips and Tutorials >  >  Verifying a credit card number

## squiggler47

Credit card numbers have a check digit at the end, this can catch a lot of typing errors.

I created a formula to test whether the number matches the check digit, note that this formula expects a 13 or 16 digit creditcard number entered in a cell formatted as text.

=(--RIGHT(B9,1)=(10-MOD(SUMPRODUCT(--MID("01234567891x3x5x7x9",MID(B9,ROW(INDIRECT("1:"&(LEN(B9)-1))),1)*(1+MOD(ROW(INDIRECT("1:"&(LEN(B9)-1))),2))+1,1)),10)))

You can try this on your own card number (I'm not giving you mine!), enter your number it should return true, change the last digit on the right  and it should return false, changing any other digit should have the same effect!

Note changing 2 digits may or may not give false, as the changes could correct each other!

----------


## martindwilson

hmm i tried mine 3732 847562 106045 it comes up false but still works with my pin which is 3657
i wonder if it's to do with the 3 digit security number on the other side mines 856 ?
or maybe its the start date of 06/10 expires 06/13 that has some bearing on the matter?

----------


## daddylonglegs

Hey, Martin are you sure the security code is correct, I couldn't make that work  :Smilie: 

Darren, I don't think that'll work if the check digit is zero. This formula theoretically works with any length card number.....

=RIGHT(B9)=MOD(SUMPRODUCT(-MID(TEXT(MID(B9,ROW(INDIRECT("1:"&LEN(B9)-1)),1)*(MOD(ROW(INDIRECT("1:"&LEN(B9)-1))+LEN(B9),2)+1),"00"),{1,2},1)),10)&""

----------


## squiggler47

yes you are right, I hadn't tested with a zero :-

=RIGHT(C3)=RIGHT(10-RIGHT(SUMPRODUCT(--MID("01234567891x3x5x7x9",MID(C3,ROW(INDIRECT("1:"&(LEN(C3)-1))),1)*(1+MOD(ROW(INDIRECT("1:"&(LEN(C3)-1))),2))+1,1))))

I modded it, I always forget the right can have no parameter, that's my BASIC grounding again, its a touch longer but looses one reference! in the original I hard coded "1:15" as there is only visa that has a 13 digit number.

----------


## Cheeky Charlie

> hmm i tried mine 3732 847562 106045 it comes up false but still works with my pin which is 3657
> i wonder if it's to do with the 3 digit security number on the other side mines 856 ?
> or maybe its the start date of 06/10 expires 06/13 that has some bearing on the matter?



Ebay
Hi Fi
Arcam
Amps
Buy it Now
Delivery address: Cheeky Charlie, 1 Cheeky Street

----------

