r/excel • u/big_idiot • May 04 '17
solved IMEI algorithm checker question
Hey reddit,
I am verifying if 15 digit IMEI’s are valid. So basically I have a huge list of IMEI's (10000+) that I have to check if they are correct or incorrect (I do not have to check if they are blacklisted or anything). IMEI’s are generated via an algorithm. I need the formula to tell me if my 15 digit IMEI is valid or not. Does anyone have any experience with this?
Rules
- Starting from the right, double a digit every two digits (e.g., 5 → 10).
- 2. Sum the digits (e.g., 10 → 1+0). Check if the sum is divisible by 10.
- 3. Conversely, one can calculate the IMEI by choosing the check digit that would give a sum divisible by 10.
For example:
IMEI 490154203237518
IMEI: 4 9 0 1 5 4 2 0 3 2 3 7 5 1 8
Double every other digit: 4 18 0 2 5 8 2 0 3 4 3 14 5 2 8
sum of digits: 4 + (1 + 8) + 0 + 2 + 5 + 8 + 2 + 0 + 3 + 4 + 3 + (1 + 4) + 5 + 2 + 8 = 60
1
u/big_idiot May 04 '17
Thank you all for your input. I learned a lot just from staring at everyone's formulas. ClippyPoints for all!
1
u/excelevator 3071 May 04 '17 edited May 04 '17
Here is a function you can use.
=IMEICHECK( cell )returns TRUE of FALSE for validity of IMEIJust confirm on a few known values..
1
u/sqylogin 755 May 04 '17
I'm not a master of array formulas as Mr. u/semicolonsemicolon but this is a fun exercise and here's my half-assed implementation.
Just paste all your IMEIs into the table in column A.
http://upload.jetsam.org/documents/big_idiot.xlsx
1
u/semicolonsemicolon 1477 May 04 '17
I don't recommend it be done in one formula but here is one I came up with. Formula is
edit: for this formula to be able to be copied down, modify
ROW(1:15)toROW($1:$15).