# Formula Challenge 023 - IMEI Luhn Check

#### MicahDail

##### New Member
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}

#### DBExcel

##### New Member
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

#### Malcolm Russell

##### New Member
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,
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.