Chinese National ID Card Validation The Chinese National ID Card is of 18 digits and has the format NNNNNN-YYYYMMDD-NNN-C (- is inserted for the sake of brevity, numbers will be without -). NNNNNN and NNN – are random numbers YYYYMMDD is Date of Birth. C – Checksum Digit C is calculated as follows from first 17 digits – i = 18, 17, 16…….4, 3, 2 Wi = 2^(i-1) mod 11 => 7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2 S = Sum of (individual digits * corresponding Wi) C = (12 – (S mod 11)) mod 11 If C is 10, then it becomes X. Ex. 215064197012068094 S = 7*2+9*1+10*5+5*0+8*6+4*4+2*1+1*9+6*7+3*0+7*1+9*2+10*0+5*6+8*8+4*0+2*9 = 327 C = (12 – (327 mod 11)) mod 11 = 4 List the valid Chinese National ID numbers from the given list.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 428
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Validate Chinese National IDs with Power Query
Power Query solution 1 for Validate Chinese National IDs, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
each try
Text.Replace(
Text.From(
Number.Mod(
12
- Number.Mod(
List.Sum(
List.Transform(
{0 .. 16},
(n) =>
Number.Mod(Number.Power(2, 17 - n), 11)
* Number.From(Text.At([National ID], n))
)
),
11
),
11
)
),
"10",
"X"
)
= Text.End([National ID], 1)
and Value.Is(Date.From(Text.Middle([National ID], 6, 8)), type date)
otherwise
false
)
in
AnsPower Query solution 2 for Validate Chinese National IDs, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Generate = List.Reverse(List.Transform({1 .. 17}, each Number.Mod(Number.Power(2, _), 11))),
Return = Table.SelectRows(
Source,
each try
[
S = Text.ToList([National ID]),
N = List.Transform(S, Number.From),
F17 = List.FirstN(N, 17),
Z = List.Zip({Generate, F17}),
P = List.Transform(Z, List.Product),
T = List.Sum(P),
R = Number.Mod(12 - Number.Mod(T, 11), 11) = (try N{17} otherwise 10)
][R]
otherwise
false
)
in
ReturnPower Query solution 3 for Validate Chinese National IDs, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Wi = List.Transform({1..17}, each Number.Mod(Number.Power(2, 18 - _), 11)),
DotProduct = (A as list, B as list) as number =>
List.Sum(List.Transform(List.Zip({A, B}), List.Product)),
C = (S as number) as text =>
let
c = Number.Mod(12 - Number.Mod(S, 11), 11)
in
Text.Replace(Text.From(c), "10", "X"),
Checksum = (txt as text) as text =>
let
digits = List.Transform(Text.ToList(txt), Number.From)
in
try C(DotProduct(digits, Wi)) otherwise "E", // E for Error
IsValidDate = (txt as text) as logical => not (try Date.From(txt))[HasError],
AddCustomCol = Table.AddColumn(Source, "IsValidID", each
let
ID = Text.Start([National ID], 17),
CheckDigit = Text.End([National ID], 1),
DoB = Text.Middle([National ID], 6, 8)
in
IsValidDate(DoB) and (Checksum(ID) = CheckDigit)
),
ValidRows = Table.SelectRows(AddCustomCol, each [IsValidID])
in
ValidRows
Brian Julius, here's an example that is far from minimal in length but is intended to be a modular decomposition of the problem into logical components.
Power Query solution 4 for Validate Chinese National IDs, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
R = Table.AddColumn(
S,
"Answer Expected",
each
let
n = [National ID],
a = Text.ToList(n),
b = List.Transform(List.RemoveLastN(a), each try Number.From(_) otherwise null),
c = List.Transform(List.Reverse({2 .. 18}), each Number.Mod(Number.Power(2, _ - 1), 11)),
d = List.Sum(List.Transform({0 .. List.Count(c) - 1}, each b{_} * c{_})),
e = Number.Mod(12 - Number.Mod(d, 11), 11),
f = if e = 10 then "X" else e,
g = if Text.End(n, 1) = Text.From(f) then n else null
in
g
),
h = R[[Answer Expected]],
Sol = Table.SelectRows(h, each [Answer Expected] <> null)
in
SolSolving the challenge of Validate Chinese National IDs with Excel
Excel solution 1 for Validate Chinese National IDs, proposed by Bo Rydobon 🇹🇭:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(a,
LET(s,
SEQUENCE(
17
),
IFERROR(TEXT(
MID(
a,
7,
8
),
"0-00-00"
)*(RIGHT(
a
)=SUBSTITUTE(MOD(12-MOD(SUM(MOD(2^(18-s),
11)*MID(
a,
s,
1
)),
11),
11),
10,
"X")),
)))))Excel solution 2 for Validate Chinese National IDs, proposed by Rick Rothstein:
=LET(r,
A2:A10,
s,
SEQUENCE(
17
),
FILTER(r,
IFERROR(MAP(r,
LAMBDA(a,
SUBSTITUTE(MOD(12-MOD(SUM(MOD(2^(18-s),
11)*MID(
a,
s,
1
)),
11),
11),
10,
"X")=RIGHT(
a
))),
)))Excel solution 3 for Validate Chinese National IDs, proposed by John V.:
=TOCOL(MAP(A2:A10,LAMBDA(x,LET(s,ROW(1:17),c,RIGHT(x),r,LAMBDA(n,MOD(n,11)),IFS(r(12-r(SUM(MID(x,s,1)*r(2^(18-s)))))=IF(c="x",10,--c),x)))),2)Excel solution 4 for Validate Chinese National IDs, proposed by محمد حلمي:
=LET(d,
A2:A10,
s,
SEQUENCE(
17
),
FILTER(d,
IFERROR((RIGHT(
d
)=
SUBSTITUTE(MOD(MAP(d,
LAMBDA(a,
12-SUM(MID(
a,
s,
1
)*
2^(18-s)))),
11),
10,
"X"))*TEXT(
MID(
d,
7,
8
),
"0000-00-00"
),
)))Excel solution 5 for Validate Chinese National IDs, proposed by Julian Poeltl:
=LET(F,
A2:A10,
R,
MAP(F,
LAMBDA(I,
LET(SP,
MID(
I,
SEQUENCE(
,
18
),
1
),
FS,
DROP(
SP,
,
-1
),
C,
MOD(12-(MOD(SUM(IFERROR(FS*MOD(2^(SEQUENCE(
,
17,
18,
-1
)-1),
11),
0)),
11)),
11),
CC,
IF(
C=10,
"X",
C
),
EXACT(
CC,
TAKE(
SP,
,
-1
)
)))),
FILTER(
F,
R
))Excel solution 6 for Validate Chinese National IDs, proposed by Timothée BLIOT:
=LET(A,
A2:A10,
B,
LEN(
REGEXEXTRACT(
A,
"d+X?$"
)
)=18,
Y,
--MID(
A,
7,
4
),
M,
--MID(
A,
11,
2
),
D,
--MID(
A,
13,
2
),
C,
YExcel solution 7 for Validate Chinese National IDs, proposed by Hussein SATOUR:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
y,
LET(
a,
MOD(
12-MOD(
SUMPRODUCT(
--MID(
y,
SEQUENCE(
LEN(
y
)-1
),
1
),
MOD(
2^SEQUENCE(
17,
,
17,
-1
),
11
)
),
11
),
11
),
IFERROR(
IF(
a=10,
"X",
TEXT(
a,
"@"
)
)=RIGHT(
y
),
0
)
)
)
)
)Excel solution 8 for Validate Chinese National IDs, proposed by Oscar Mendez Roca Farell:
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(r,
ROW(
1:17
),
F,
LAMBDA(
i,
MOD(
i,
11
)
),
IF(F(12-F(SUM(MID(
a,
r,
1
)*F(2^(18-r)))))=IFERROR(
--RIGHT(
a
),
10
),
a,
1/0)))),
2)Excel solution 9 for Validate Chinese National IDs, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(t,
LET(
_s,
SUM(MID(
t,
SEQUENCE(
17
),
1
) * MOD(2 ^ (SEQUENCE(
17,
,
18,
-1
) - 1),
11)),
_c,
MOD(
12 - MOD(
_s,
11
),
11
),
_c1,
IF(
_c = 10,
"X",
_c
),
IFERROR(
TEXT(
_c1,
"0"
) = RIGHT(
t
),
FALSE
)
)
)
)
)Excel solution 10 for Validate Chinese National IDs, proposed by LEONARD OCHEA 🇷🇴:
=LET(i,
A2:A10,
s,
SEQUENCE(
,
17
),
M,
LAMBDA(
x,
MOD(
x,
11
)
),
t,
M(12-M(BYROW(MID(
i,
s,
1
)*M(2^(18-s)),
SUM))),
C,
IFERROR(
IF(
t=10,
"X",
t
),
),
FILTER(
i,
RIGHT(
i
)=C&""
))Excel solution 11 for Validate Chinese National IDs, proposed by 🇵🇪 Ned Navarrete C.:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(f,
IF(ISERR(
--LEFT(
f,
17
)
),
,
LET(i,
SEQUENCE(
17
),
c,
MOD(12-MOD(SUM(MID(
f,
i,
1
)*MOD(2^(18-i),
11)),
11),
11),
IF(
c-10,
""&c,
"X"
)=RIGHT(
f
))))))Excel solution 12 for Validate Chinese National IDs, proposed by Andy Heybruch:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(_nid,
LET(_id,
_nid,
_digits,
MID(
_id,
SEQUENCE(
LEN(
_id
)
),
1
),
_check,
IFERROR(
--TAKE(
_digits,
-1
),
"X"
),
_i,
SEQUENCE(
17,
,
18,
-1
),
_wi,
MOD(2^(_i-1),
11),
_s,
SUM(
DROP(
_digits,
-1
)*_wi
),
_c,
MOD(
12-MOD(
_s,
11
),
11
),
IFERROR(--(_check=IF(
_c=10,
"X",
_c
)),
0)=1))))Excel solution 13 for Validate Chinese National IDs, proposed by Songglod P.:
=LET(
nid,
A2:A10,
checksum,
MAP(
nid,
LAMBDA(
id,
LET(
id,
--MID(
id,
SEQUENCE(
1,
LEN(
id
)-1
),
1
),
digits,
SEQUENCE(
1,
17,
18,
-1
),
wi,
MAP(
digits,
LAMBDA(
x,
MOD(
POWER(
2,
x-1
),
11
)
)
),
s,
SUMPRODUCT(
wi,
id
),
c,
IFERROR(
MOD(
12-MOD(
s,
11
),
11
),
-1
),
IF(
c=10,
"X",
TEXT(
c,
0
)
)
)
)
),
FILTER(
nid,
RIGHT(
nid,
1
)=checksum
)
)Excel solution 14 for Validate Chinese National IDs, proposed by Ernesto Vega Castillo:
=FILTER(A2:A10,
A2:A10=IFERROR(BYROW(A2:A10,
LAMBDA(m,
LET(r,
MID(
m,
1,
LEN(
m
)-1
),
o,
MAP(LET(x,
SUM(LET(i,
LEFT(
MID(
m,
SEQUENCE(
LEN(
m
)-1
),
1
),
LEN(
m
)-1
)*1,
Wi,
MOD(2^(SEQUENCE(
17,
,
18,
-1
)-1),
11),
i*Wi)),
x),
LAMBDA(w,
LET(z,
MOD(12-(MOD(
SUM(
w
),
11
)),
11),
IF(
z<>10,
z,
"X"
)))),
r&o))),
""))Excel solution 15 for Validate Chinese National IDs, proposed by Ben Gutscher:
=TOCOL(MAP(A2:A10,
LAMBDA(id,
LET(digits,
SUBSTITUTE(
MID(
id,
SEQUENCE(
,
18
),
1
),
"X",
& 10
),
Wi,
MOD(2^(SEQUENCE(
,
17,
18,
-1
)-1),
11),
S,
SUM(
TAKE(
digits,
,
17
)*Wi
),
C,
MOD(
12-MOD(
S,
11
),
11
),
IF(
NUMBERVALUE(
TAKE(
digits,
,
-1
)
)=C,
id,
NA()
)))),
3)Solving the challenge of Validate Chinese National IDs with Python
Python solution 1 for Validate Chinese National IDs, proposed by Konrad Gryczan, PhD:
import pandas as pd
from datetime import datetime
input = pd.read_excel("428 Chinese National ID.xlsx", usecols="A", nrows=10)
test = pd.read_excel("428 Chinese National ID.xlsx", usecols = "B", nrows = 4)
general_pattern = "\d{6}\d{8}\d{3}[0-9X]"
def is_valid_date(ID):
date_str = ID[6:14]
try:
datetime.strptime(date_str, "%Y%m%d")
return True
except ValueError:
return False
def is_ID_valid(ID):
base = [int(digit) for digit in ID[:17]]
I = list(range(18, 1, -1))
WI = [2**(i-1) % 11 for i in I]
S = sum([digit * weight for digit, weight in zip(base, WI)])
C = (12 - (S % 11)) % 11
C = 'X' if C == 10 else str(C)
whole_id = ''.join(map(str, base)) + C
return whole_id == ID
r1 = input.copy()
r1 = input[input['National ID'].str.match(general_pattern).fillna(False)]
r1 = r1[r1['National ID'].apply(is_valid_date)]
r1 = r1[r1['National ID'].apply(is_ID_valid)].reset_index(drop=True)
r1.rename(columns={"National ID": "Answer Expected"}, inplace=True)
print(r1.equals(test)) # True
Solving the challenge of Validate Chinese National IDs with R
R solution 1 for Validate Chinese National IDs, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/428 Chinese National ID.xlsx", range = "A1:A10")
test = read_excel("Excel/428 Chinese National ID.xlsx", range = "B1:B5")
general_pattern = "\d{6}\d{8}\d{3}[0-9X]"
is_valid_date = function(ID) {
str_sub(ID, 7, 14) %>% ymd()
if (is.na(date)) {
return(FALSE)
} else {
return(TRUE)
}
}
is_ID_valid = function(ID) {
base = str_sub(ID, 1, 17) %>% str_split("") %>% unlist() %>% as.numeric()
I = 18:2
WI = 2**(I-1) %% 11
S = sum(base * WI)
C = (12 - (S %% 11)) %% 11
C = as.character(C) %>% str_replace_all("10", "X")
whole_id = base %>% str_c(collapse = "") %>% str_c(C)
return(whole_id == ID)
}
r1 = input %>%
mutate(gen_pattern = str_match(`National ID`, general_pattern)) %>%
mutate(dob = str_sub(`National ID`, 7, 14) %>% ymd()) %>%
mutate(is_valid = map_lgl(`National ID`, is_ID_valid)) %>%
filter(is_valid == TRUE & !is.na(dob) & !is.na(gen_pattern)) %>%
select(`Answer Expected` = `National ID`)
&&