• 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


  • 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().

{=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) =>


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)

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) =>
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)

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)
Why doesn't it surprise me that Lorimer managed to come up with amazing solutions!
Now, 10 years on and with an entirely new calculation engine, one could have
= MAP(listIMEI, Checkdigitλ)


= LET(
    digits,      EXPLODEλ(IMEI),
    LuhnDigit,   VALUE(TAKE(digits, -1)),
    pairs,       WRAPROWS(DROP(digits, -1), 2),
    weighted,    {1, 2} * pairs,
    tens,        QUOTIENT(weighted, 10),
    checkdigit,  10 - MOD(SUM(weighted, tens), 10),
    checkdigit = LuhnDigit
Note: Unlike the traditional challenge there is no attempt to go for brevity, I prize clarity more highly.


  • Chandoo56720Calulation.xlsx
    37.2 KB · Views: 1
where A2 contains the IMEI number. It returns TRUE if the 15th digit is correct.
Last edited: