Luhn algorithm in excel

[nashika]

I would like to generate a luhn-number in excel. For example: 9012028684 then my luhn-number is 5 so the new number is 90120286845.
How can I do it in excel with 1100 row with one formula? Can anybody help me to write a right formula.
Thanks in advance.

Nash

 

[hippiehacker]

I would like to generate a luhn-number in excel. For example: 9012028684 then my luhn-number is 5 so the new number is 90120286845.
How can I do it in excel with 1100 row with one formula? Can anybody help me to write a right formula.
Thanks in advance.

Nash

name a cell 'Luhn' or whatever you prefer (named range)

and use in a cell like this

= 9012028684 &Luhn

 

[nashika]

Sorry but it doesn't work ... excel write it's wrong reference. Any idea or an example where I can see how to use it?

 

[pgc01]

Hi nashika

If you want to use a formula, there's a thread about it here:

http://www.mrexcel.com/forum/lounge-v-2-0/364672-tough-problem-3-formula.html

You can try Barry's formula in that thread in post #16, in B1:

=MOD(SUMPRODUCT(-MID(TEXT(MID(TEXT(A1,REPT(0,15)),ROW(INDIRECT("1:15")),1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)),10)

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">9012028684</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet3</td></tr></table>

 

[nashika]

Hi,

Unfortunately i tryed what you suggested, but my excel write something wrong with formula(red text) when i pasted it.
=MOD(SUMPRODUCT(-MID(TEXT(MID(TEXT(A1,REPT(0,15)),ROW(INDIRECT("1:15")),1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)),10)

So I can't use it, any idea?
Sorry for my unskillfulls.

 

[pgc01]

Hi

As you can see in the link I posted the formulas have been tested.

Maybe your excel or windows installations are not English and you must use other separators?

 

[DeusXv]

Yeah it worked for me aswell

Excel 2007
	A	B	C
2	9012028684		5
3	90120286845
Sheet1

Cell Formulas
Range	Formula
C2	=MOD(SUMPRODUCT(-MID(TEXT(MID(TEXT(A2,REPT(0,15)),ROW(INDIRECT("1:15")),1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)),10)
A3	=CONCATENATE(A2&C2)

I was just wondering is this supposed to continue on , so the formula then calculates the next Luhn number which would be 6 and then adds that to the end of the number. Or does it just run once ?

 

[pgc01]

I was just wondering is this supposed to continue on , so the formula then calculates the next Luhn number which would be 6 and then adds that to the end of the number. Or does it just run once ?

Hi

It usually is used once as a control digit.

For ex., as I wrote in the link, you have them in the credit cards. They have usually 16 digits, the last one is calculated using this algorithm.

Try with your own credit cards. Enter the first 15 digits and check the 16th.

 

[VoG]

Try with your own credit cards. Enter the first 15 digits and check the 16th.

And please post the result

 

[DeusXv]

And please post the result

I was about the say the same

But really guys share your results using your own credit cards so we can see if the formula worked, I'll post mine as soon as I book some flights for a holiday this year