• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 023 - IMEI Luhn Check

Here's my contribution. Below is an actual implementation of the Luhn algorithm (according to Wikipedia and confirmed by http://www.ee.unb.ca/cgi-bin/tervo/luhn.pl). Whether a digit is multiplied by 2 actually depends on the relationship between that digit and the end of the integer, . Also, it allows a check digit value of 0.

This utilizes ROW(INDIRECT()) to account for integers of varying length. Props to Lori for her brilliant use of TEXT().

Code:
{=MOD(SUM(--TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*IF(ISODD(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))),2,1),"0 0\/1")),10)=0}
 
Solved in Excel PowerQuery Add-in - Longer but much easier to follow especially with the help of the Query Editor.

My solution also gives the Luhn digit if given an input of 14 digits and throws an easy to comprehend error message if given an invalid input. See demo video here

Full query function text:

(Input as any) =>

let

TextControl = try Number.ToText(Input) otherwise Input,
ListChars = Text.ToList(TextControl),
CheckFigs = List.FirstN(ListChars, 14),
ControlChar = if List.Count(ListChars) = 15 then ListChars{14} else null,
EveryOther = List.Alternate(CheckFigs, 1, 1, 0),
NonDoubled = List.Difference(CheckFigs, EveryOther),
DoubledEveryOther = List.Transform(EveryOther, (_) => Number.ToText(List.Product({Number.From (_), 2}))),
SplitoutDigits = List.Transform(DoubledEveryOther, (_) => Text.ToList(_)),
CombineSubDigits = List.Combine(SplitoutDigits),
CombineCheckFigs = List.Transform(List.Combine({CombineSubDigits, NonDoubled}), (_) => Number.From(_)),
SumAllDigits = List.Sum(CombineCheckFigs),
IMEIResult = 10 - Number.Mod(SumAllDigits, 10),

Output = if List.Count(ListChars) = 14
then IMEIResult
else if (List.Count(ListChars) <> 15)
then Error.Record("Required Numeric Input of Length 14 or 15.", "Error in Input")
else IMEIResult = Number.From(ControlChar)

in
Output
 
Hi DBExcel,

The Powerquery solution was awesome and saved me heaps of time.
I had to convert it to 9 digit validation.
I did find 2 bugs however and thought I would share,
NonDoubled = List.Difference(CheckFigs, EveryOther) would occasionally product the wrong output. I changed it to,
NonDoubled = List.Alternate(CheckFigs,1,1,1), instead.
Also LuhnResult can return a value of 10 instead of 0 so inputs with a controlchar of 0 would fail.

My function converted from yours to validate 9 digits,


(Input as any) =>
let
ToText = try Number.ToText(Input) otherwise Input,
ListChars = Text.ToList(ToText),
CheckFigs = List.FirstN(ListChars,8),
ControlChar = if List.Count(ListChars) = 9 then ListChars{8} else null,
EveryOther = List.Alternate(CheckFigs,1,1,0),
NonDoubled = List.Alternate(CheckFigs,1,1,1),
DoubleEveryOther = List.Transform(EveryOther,(_) => Number.ToText(List.Product({Number.From (_),2}))),
SplitOutDigits = List.Transform(DoubleEveryOther, (_) => Text.ToList(_)),
CombineSubDigits = List.Combine(SplitOutDigits),
CombineCheckFigs = List.Transform(List.Combine({CombineSubDigits,NonDoubled}),(_) => Number.From(_)),
SumAllDigits = List.Sum(CombineCheckFigs),
Mod10 = Number.Mod(SumAllDigits,10),
LuhnResult = if Mod10 = 0 then 0 else 10 - Mod10,

Output = if (List.Count(ListChars) <> 9) then false
else LuhnResult = Number.From(ControlChar)
in
Output


Thanks
Malcolm.
 
Here is a formula, based on Somendra's but with additional logic to account for a checksum of zero. It returns TRUE or FALSE unless the number of characters is wrong in which case it is #VALUE!. Note the IMEI is in column J (and SIM in K)
=IF((RIGHT(J2,1)+0=0),10, RIGHT(J2,1)+0 )=10-MOD(SUMPRODUCT(INT(2*(MID(J2,2*ROW($1:$7),1)+0)/10)+MOD(2*(MID(J2,2*ROW($1:$7),1)+0),10)+(MID(J2,(2*ROW($1:$7))-1,1)+0)),10)

And as they are related, modifying for SIM number (19 digits)
=IF((RIGHT(K2,1)+0=0),10, RIGHT(K2,1)+0 )=10-MOD(SUMPRODUCT(INT(2*(MID(K2,2*ROW($1:$9),1)+0)/10)+MOD(2*(MID(K2,2*ROW($1:$9),1)+0),10)+(MID(K2,(2*ROW($1:$9))-1,1)+0)),10)
 
Back
Top