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!
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
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?
Last edited by martindwilson; 01-23-2011 at 09:26 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hey, Martin are you sure the security code is correct, I couldn't make that work
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)&""
Audere est facere
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.
Regards
Darren
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
EbayOriginally Posted by martindwilson
Hi Fi
Arcam
Amps
Buy it Now
Delivery address: Cheeky Charlie, 1 Cheeky Street
CC
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks