This time challenge is to come up with a formula (or a VBA function though formula is preferred) to give the result of TRUE or FALSE if a 16 digit credit card number can be validated or not using Mod 10 algorithm. The credit card numbers will be stored as Text as Excel can't contain more than 15 significant digits in numeric format.
Below is the way Credit Card Numbers can be verified.
1. Start from the rightmost digit and multiply every odd position digit by 1 starting from rightmost. Hence, 1st, 3rd, 5th….15th digits from rightmost should be multiplied by 1.
2. Start from second rightmost digits and multiply this 2. Multiple every even position digit by 2 starting from 2nd rightmost digit. Hence, 2nd, 4th, 6th….16th digits from rightmost should be multiplied by 2.
3. Perform numerological sum on digits which we got in step 2 if result is > 9.
4. Add the results of step 1 and step 3.
5. This result should be a multiple of 10 if the credit card number is valid.
Look at the below example (Row 1 has credit card number. The numbers are in different cell just to make our understanding correct. The formula / VBA function which you will make will be for all 16 digits in one cell only) –
Hence, if cell A1 contains following credit card numbers, your result should be as follows for following sample numbers –
5026209217581350 – TRUE
7006688888881300 – FALSE
4406212008581350 – TRUE
6809008888881300 – TRUE
6839808008881306 – FALSE
The result would be published after one month i.e. on 1-Mar-16.
I am impressed with your work and skill. Thank you so much.
That's genius, great work, I'm very impressed
Thanks for sharing your knowledge and insight. I'd always wondered how to cross check the numbers
For the longest time I've looked for a method to try to discern true credit numbers from made up numbers.. This will help immensely