Luhn algorithm in excel

N

nashika

New Member
Joined
Sep 2, 2013
Messages
3
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
 
Sort by date Sort by votes
VoG said:
And please post the result :ROFLMAO:
Click to expand...

Wow, this formula works perfectly on Visa and MC ... and also with American Express, gas cards, Discover, and with cards that have less than 15 digits!

Here's the results for the last digit on seven of my credit cards which I tested. The formula was right every time for the last digit. Amazing!

7
3
9
1
7
1
1

<colgroup><col style="width: 48pt;" width="64"> <tbody>
</tbody>
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
DeusXv said:
Yeah it worked for me aswell

Excel 2007
ABC
290120286845
390120286845

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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 ?
Click to expand...

Hello
This formula is great and works perfect with under 15 numbers. My dilemma is i need to make this formula to work with 25 numbers (or less) but unfortunately i am little rusty with my excell skills and math . LOL. Please anyone help. Thank you in advance
 
Upvote 0
Quote
My dilemma is i need to make this formula to work with 25 numbers (or less) but unfortunately i am little rusty with my excell skills and math . LOL. Please anyone help.

See the suggestions #18 in the referenced posts by barry houdini; Jan 17th, 2009


Excel 2010
ABCDEFG
1
2901202868455510
39012028684901202868451246999012028684901202868451246925
4
2a
Cell Formulas
RangeFormula
B2=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)
C2=MOD(LEN(SUBSTITUTE(A2,0,""))-LEN(A2)-SUMPRODUCT((MOD(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2)))+LEN(A2)+1,2)+1)-1,9)+1)),10)
C3=MOD(LEN(SUBSTITUTE(A3,0,""))-LEN(A3)-SUMPRODUCT((MOD(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A3)))+LEN(A3)+1,2)+1)-1,9)+1)),10)
D2=MOD(SUMPRODUCT(-MID(TEXT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2)))+LEN(A2)+1,2)+1),"00"),{1,2},1)),10)
D3=MOD(SUMPRODUCT(-MID(TEXT(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A3)))+LEN(A3)+1,2)+1),"00"),{1,2},1)),10)
G2=LEN(A2)
G3=LEN(A3)
F3=A3&C3
 
Upvote 0
Dave Patton said:
Quote
My dilemma is i need to make this formula to work with 25 numbers (or less) but unfortunately i am little rusty with my excell skills and math . LOL. Please anyone help.

See the suggestions #18 in the referenced posts by barry houdini; Jan 17th, 2009

Excel 2010
ABCDEFG
1
2901202868455510
39012028684901202868451246999012028684901202868451246925
4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
2a​

Worksheet Formulas
CellFormula
B2=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)
C2=MOD(LEN(SUBSTITUTE(A2,0,""))-LEN(A2)-SUMPRODUCT((MOD(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2)))+LEN(A2)+1,2)+1)-1,9)+1)),10)
D2=MOD(SUMPRODUCT(-MID(TEXT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2)))+LEN(A2)+1,2)+1),"00"),{1,2},1)),10)
C3=MOD(LEN(SUBSTITUTE(A3,0,""))-LEN(A3)-SUMPRODUCT((MOD(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A3)))+LEN(A3)+1,2)+1)-1,9)+1)),10)
D3=MOD(SUMPRODUCT(-MID(TEXT(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A3)))+LEN(A3)+1,2)+1),"00"),{1,2},1)),10)
G2=LEN(A2)
F3=A3&C3
G3=LEN(A3)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Click to expand...
Thank you so much for your quick respond.
 
Upvote 0
Dave Patton said:
Quote
My dilemma is i need to make this formula to work with 25 numbers (or less) but unfortunately i am little rusty with my excell skills and math . LOL. Please anyone help.

See the suggestions #18 in the referenced posts by barry houdini; Jan 17th, 2009

Excel 2010
ABCDEFG
1
2901202868455510
39012028684901202868451246999012028684901202868451246925
4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
2a​

Worksheet Formulas
CellFormula
B2=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)
C2=MOD(LEN(SUBSTITUTE(A2,0,""))-LEN(A2)-SUMPRODUCT((MOD(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2)))+LEN(A2)+1,2)+1)-1,9)+1)),10)
D2=MOD(SUMPRODUCT(-MID(TEXT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2)))+LEN(A2)+1,2)+1),"00"),{1,2},1)),10)
C3=MOD(LEN(SUBSTITUTE(A3,0,""))-LEN(A3)-SUMPRODUCT((MOD(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A3)))+LEN(A3)+1,2)+1)-1,9)+1)),10)
D3=MOD(SUMPRODUCT(-MID(TEXT(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A3)))+LEN(A3)+1,2)+1),"00"),{1,2},1)),10)
G2=LEN(A2)
F3=A3&C3
G3=LEN(A3)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

Hi.thank you again for your help. I have one more question. the formulas in C3 and D3 and the formulas in B2,C2 and D2 are they all same formulas using different functions or they depend on each other and each one need to be used so the other can work. I am new here and unfortunately i cant find the post you are referring to. Is it possible to share a link. Thank you again
 
Upvote 0
Dave Patton said:
The post that was mentioned is
https://www.mrexcel.com/forum/lounge-v-2-0/364672-tough-problem-3-formula.html

You can choose the alternative that you prefer.
Click to expand...

So they are alternatives. This is what i thought since they all work separately but wasnt sure since the formulas are a way out of my math. :LOL:
Also i noticed the 15 numbers one is working with 25 numbers too and gives correct result even in the formula the weight is set for only 15 numbers. not sure why and how but as i said a way out of my math. Thank you again for the quick response.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Can I combine randbetween, rept, and vlookup to get multiple answers?
Replies
4
Views
158
Marla89
M
A
  • Solved
Automatic Serial Number before Text in a Cell
2 3
Replies
25
Views
526
Joe4
Joe4
H
  • Solved
YTD total at change of year
Replies
9
Views
160
myall_blues
myall_blues
J
  • Question
Conditional Formatting.. The bane of my Excel Existence
Replies
2
Views
166
RoryA
RoryA
O
  • Solved
Managing Random Numbers
Replies
6
Views
157
smozgur
smozgur

Forum statistics

Threads
1,216,077
Messages
6,128,685
Members
449,463
Latest member
Jojomen56

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top