14 digit Check Digit Calculation in Luhn algorithm

[zhosen]

Hi,
I am new comer here. I need a favor to make a formula in Excel to create series of number for 14 Digit Check Digit. Here is my requirements-
I have 14 Digit of data [IMEI number] and i want to make the 15th digit as check digit as per Luhn algorithm. For example-

IMEI: 35145120840121
(5x2, 4x2, 1x2, 0x2, 4x2, 1x2, 1x2) = (10, 8, 2, 0, 8, 2, 2)
(1+0+8+2+0+8+2+2) + (3+1+5+2+8+0+2 ) = 44
Luhn Digit : 6

And final digit will be: 351451208401216
So, how to make the formula in excel? Please help me to find it out. I will appreciate if anybody can help me. thanks in advance. Have a great day!

 

[nuked]

This might help

http://www.mrexcel.com/forum/excel-questions/266214-luhn-check-digit-formula.html

 

[zhosen]

This might help

http://www.mrexcel.com/forum/excel-questions/266214-luhn-check-digit-formula.html

Hi,
Thanks for your reply. I check the whole post but it seems that is not working for me. I need it for 14 digit. Please reply me if there is any idea. thanks!

 

[nuked]

Alt+F11
Click Insert => Module
Paste the following code:

Option Explicit

Function AddLuhnDigit(target As Range) As String
Dim sTmp As String
Dim i As Integer, iTmp As Integer

For i = 2 To Len(target.Text) Step 2
    sTmp = sTmp & (CInt(Mid(target.Text, i, 1)) * 2)
Next i

For i = 1 To Len(sTmp)
    iTmp = iTmp + CInt(Mid(sTmp, i, 1))
Next i

For i = 1 To Len(target.Text) Step 2
    iTmp = iTmp + CInt(Mid(target.Text, i, 1))
Next i

AddLuhnDigit = target.Text & (10 - (iTmp Mod 10))

End Function

You can then use it on your sheet like any other formula, so assuming your list of numbers is in column A, then put =AddLuhnDigit(A1) in B1 and copy down.

Let me know if it works.

 

[zareva]

Thank you nuked. Your code is great. I tried it and it works but it displays 10 instead of 0 if the check digit is 0. Please update the code so as if the check digit is 0, the code to dialysis 0 not 10. Are you able to do so?

Thank you in advance and warm regards.

 

[nuked]

Hi
Try:

 AddLuhnDigit = target.Text & Iif((10 - (iTmp Mod 10))=10,"0",(10 - (iTmp Mod 10)))

 

[zareva]

Hi nuked,
Now it works. Thank you very much. It will help me a lot at my school excel project. Yo are a master

Regards

 

[coder4learning]

please help!!!! first code worked perfectly got the same issue of displaying 10 instead of 0 if the check digit is 0. I tried the code under below but receive a compile error

 

[johnnyL]

Golly I did this so many years ago. @coder4learning please start your own thread with your question and refer back to this thread if you need to.