List the valid South African National IDs (person identification numbers). A South African person identification number is a 13-digit number containing only numeric characters, and no whitespace, punctuation, or alpha characters. It is defined as YYMMDDSSSSCAZ: YYMMDD – Date of birth; SSSS – Female = 0 to 4999; Male = 5000 to 9999 C – Citizen = 0; Not citizen = 1 A – Any random number Z – Checksum digit. The checksum digit is calculated using the Luhn algorithm A = the sum of the digits in the ID number in the odd positions (excluding Z) B = the number formed by the concatenation of the digits in the ID number in the even positions C = the sum of the digits in (2 * B) D = A + C Z = 10 – (D mod 10)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 342
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List valid South African National IDs with Power Query
Power Query solution 1 for List valid South African National IDs, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.SelectRows(
Source,
each
let
t = Text.Select([SA IDs], {"0" .. "9"}),
x = (t, p) => Number.From(Text.ToList(t){p}),
n = List.Transform(
{0 .. Text.Length(t) - 1},
(i) =>
let
a = Text.From((if Number.IsOdd(i) then 2 else 1) * x(t, i))
in
List.Accumulate({0 .. Text.Length(a) - 1}, 0, (s, c) => s + x(a, c))
)
in
if t <> [SA IDs] then
false
else if Text.Length(t) <> 13 then
false
else if (
try
Date.FromText(Text.Middle(t, 0, 6), [Format = "yyMMdd", Culture = "en-US"])
otherwise
null
)
= null
then
false
else if n{10} > 1 then
false
else if n{12} <> 10 - Number.Mod(List.Sum(List.RemoveLastN(n)), 10) then
false
else
true
)
in
SPower Query solution 2 for List valid South African National IDs, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.SelectRows(Source, each
let //Date
a = List.Split(Text.ToList([SA IDs]), 6),
b = List.Transform(List.Split(a{0}, 2), each Number.From(Text.Combine(_))),
c = try hashtag#date(2000+b{0}, b{1}, b{2}) is date otherwise false,
//Male or Female
d = Number.From(Text.Combine(List.FirstN(a{1},4))) is number,
//Citizen or not
e = if a{1}{4}>"1" then false else true,
//Random number
f = Number.From(a{1}{5}) is number,
//Number length
g = Text.Length([SA IDs])=13,
//Checksum digit
z = let
A = List.Sum(List.Transform(List.Alternate(List.RemoveLastN(Text.ToList([SA IDs])), 1,1,1), Number.From)),
B = List.Alternate(List.RemoveLastN(Text.ToList([SA IDs])), 1,1),
C = List.Sum(List.Transform(B, each List.Sum(List.Transform(Text.ToList(Text.From(Number.From(_)*2)), Number.From)))),
D = A + C,
Z = try (if Number.Mod(D,10) = 0 then 0 else 10-Number.Mod(D,10)) = Number.From(a{2}{0}) otherwise false
in Z
in List.AllTrue({c,d,e,f,g,z}))
in
Sol
Solving the challenge of List valid South African National IDs with Excel
Excel solution 1 for List valid South African National IDs, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(m,
MID(
a,
SEQUENCE(
6,
2
),
1
)*{1,
2},
IFS((20&TEXT(
LEFT(
a,
6
),
"0-00-00"
))*(MID(
a,
11,
1
)<"2")*(MOD(
SUM(
IF(
m,
MOD(
m-1,
9
)+1
)
),
-10
)=-RIGHT(
a
)),
a)))),
3)Excel solution 2 for List valid South African National IDs, proposed by John V.:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(i,
{1,
2}*MID(
x,
SEQUENCE(
6,
2
),
1
),
IFS((RIGHT(10-RIGHT(SUM(i-9*(i>9))))=MID(
x,
13,
1
))*(19&TEXT(
LEFT(
x,
6
),
"0-00-00"
))*(--MID(
x,
11,
1
)<2),
x)))),
2)Excel solution 3 for List valid South African National IDs, proposed by محمد حلمي:
=TOCOL(MAP(A2:A10,LAMBDA(a,LET(s,SEQUENCE(6)*2,
a/AND(MOD(SUM(-MID(a,s-1,1),-("0"&MID(MID(a,s,1)*2,{1,2},1))),10)=--RIGHT(a),--MID(a,{3,5},2)<{13,32},
OR(MID(a,11,1)={0,1}&""))))),2)Excel solution 4 for List valid South African National IDs, proposed by Kris Jaganah:
=TEXT(TOCOL(A2:A10/MAP(A2:A10,
LAMBDA(x,
LET(a,
SEQUENCE(
12
),
b,
MID(
x,
a,
1
)*(MOD(
a-1,
2
)+1),
(RIGHT(
x
)=RIGHT(
10-MOD(
SUM(
IF(
b>9,
1+RIGHT(
b
),
b
)
),
10
)
))*(LEN(
x
)=13)*(--MID(
x,
3,
2
)<13)*(--MID(
x,
5,
2
)<31)*(--MID(
x,
11,
1
)<2)))),
3),
"#")Excel solution 5 for List valid South African National IDs, proposed by Julian Poeltl:
=FILTER(A2:A10,
IFERROR(MAP(A2:A10,
LAMBDA(N,
LET(SQ,
SEQUENCE(
12
),
C,
MID(
N,
SQ,
1
)*(MOD(
SQ-1,
2
)+1),
S,
MID(
N,
SQ,
1
),
R,
(--MID(
N,
3,
2
)<13)*(--MID(
N,
5,
2
)<32)*(--MID(
N,
11,
1
)<2)*(RIGHT(
N
)=RIGHT(
10-MOD(
SUM(
IF(
C>9,
1+RIGHT(
C
),
C
)
),
10
)
)),
R))),
0))Excel solution 6 for List valid South African National IDs, proposed by Timothée BLIOT:
=TOCOL(MAP(A2:A10,
LAMBDA(z,
LET(Y,
--MID(
z,
1,
2
),
M,
--MID(
z,
3,
2
),
D,
--MID(
z,
5,
2
),
T,
DATE(
--Y,
M,
D
),
A,
AND(
MONTH(
T
)=M,
DAY(
T
)=D
),
B,
SUM(--((--MID(
z,
11,
1
))={1,
0}))=1,
E,
--WRAPROWS(
MID(
z,
SEQUENCE(
LEN(
z
)-1,
1
),
1
),
2
),
F,
CONCAT(
TAKE(
E,
,
-1
)
),
G,
--RIGHT(
10-MOD(
SUM(
TAKE(
E,
,
1
)
)+SUM(
--MID(
2*F,
SEQUENCE(
LEN(
2*F
)
),
1
)
),
10
)
)=--RIGHT(
z
),
z/AND(
A,
B,
LEN(
z
)=13,
G
)))),
3)Excel solution 7 for List valid South African National IDs, proposed by Hussein SATOUR:
=LET(a, TRIM(SUBSTITUTE(A2:A10, " ", "")), b, FILTER(a, (LEN(a)=13)), c, FILTER(b, (TEXT(DATE(LEFT(b,2), MID(b,3,2), MID(b,5,2)),"yymmdd") = LEFT(b, 6)) * (--MID(b, 7, 4) <= 9999) * (--MID(b, 11, 1) < 2)), FILTER(c, MAP(c, LAMBDA(x, RIGHT(10 - MOD(SUM(--MID(x, SEQUENCE(6,,1,2),1)) + SUM(IFERROR(MID(CONCAT(MID(x, SEQUENCE(6,,2,2),1))*2, SEQUENCE(15), 1)+0,0)), 10)) = RIGHT(x)))))Excel solution 8 for List valid South African National IDs, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
_c1,
--MID(
a,
11,
1
) <= 1,
_c2,
ISNUMBER(
DAY(
20 & TEXT(
LEFT(
a,
6
),
"0-00-00"
)
)
),
_c3,
LET(
_n,
CONCAT(
MID(
a,
SEQUENCE(
6,
2
),
1
) * {1,
2}
),
_nsum,
SUM(
--MID(
_n,
SEQUENCE(
LEN(
_n
)
),
1
)
),
IFERROR(
10 - IF(
MOD(
_nsum,
10
) = 0,
10,
MOD(
_nsum,
10
)
) = RIGHT(
a
) + 0,
FALSE
)
),
AND(
_c1,
_c2,
_c3
)
)
)
)
)Excel solution 9 for List valid South African National IDs, proposed by Stefan Olsson:
=FILTER(
A2:A10,
BYROW(
A2:A10,
LAMBDA(
_id,
LET(
_dc,
ISDATE(
REGEXREPLACE(
_id&"",
"^(dd)(dd)(dd).*",
"19$1-$2-$3"
)
),
_A,
SUM(
--REGEXEXTRACT(
_id&"",
REPT(
"(.).",
6
)
)
),
_B,
TEXTJOIN(
,
1,
REGEXEXTRACT(
_id&"",
REPT(
".(.)",
6
)
)
)*2,
_C,
SUM(
--REGEXEXTRACT(
_B&"",
REPT(
"(.)",
LEN(
_B
)
)
)
),
_D,
_A+_C,
_Z,
10-MOD(
_D,
10
),
IF(
_dc,
--RIGHT(
_id,
1
)=_Z,
FALSE
)
)
)
)
)Excel solution 10 for List valid South African National IDs, proposed by Abdelrahman Omer, MBA, PMP:
=FILTER(A2:A10,0Solving the challenge of List valid South African National IDs with Python in Excel
Python in Excel solution 1 for List valid South African National IDs, proposed by Alejandro Campos:
To check it, can use a Luhn calculator, Like https://es.planetcalc.com/2461/
import re
ids = xl("A1:A10", headers=True)
ids = ids['SA IDs'].tolist()
def is_valid_sa_id(id_number):
if not re.match(r'^d{13}$', id_number):
return False
birth_date = id_number[:6]
gender_digits = int(id_number[6:10])
citizenship_digit = int(id_number[10])
checksum_digit = int(id_number[12])
if gender_digits < 0 or gender_digits > 9999:
return False
if citizenship_digit not in [0, 1]:
return False
A = sum(int(id_number[i]) for i in range(0, 12, 2))
B = int(''.join(id_number[i] for i in range(1, 12, 2)))
C = sum(int(digit) for digit in str(2 * B))
D = A + C
Z = (10 - (D % 10)) % 10
return Z == checksum_digit
valid_ids = [id_number for id_number in ids if is_valid_sa_id(id_number)]
valid_ids
Suma de verificación de Luhn
Calcula la suma de verificación utilizando el algoritmo de Luhn. El algoritmo de Luhn es popular en la validación de números de tarjetas de crédito.Solving the challenge of List valid South African National IDs with R
R solution 1 for List valid South African National IDs, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("South Africa National ID Validation.xlsx", range = "A1:A10") %>% janitor::clean_names()
test = read_excel("South Africa National ID Validation.xlsx", range = "B1:B4") %>% janitor::clean_names()
validate_id = function(number) {
digits = strsplit(as.character(number), "")[[1]]
odd_digits = digits[seq(1, 12, 2)]
even_digits = digits[seq(2, 12, 2)]
dob = str_sub(number, 1&, 6) %>% as.Date(format = "%y%m%d")
A = sum(as.numeric(odd_digits))
B = str_c(even_digits, collapse = "") %>% as.numeric()
B2 = B * 2
C = strsplit(as.character(B2), "")[[1]] %>% as.numeric() %>% sum()
D = A + C
Dmod = D %% 10
Z13 = str_sub(number, 13, 13) %>% as.numeric()
Z = ifelse(Dmod == 0, 0, 10 - D %% 10)
is_Z_valid = Z == Z13
is_status_valid = str_sub(number, 11, 11) %>% as.numeric() %>% between(0, 1)
is_valid_length = length(digits) == 13
is_date_valid = !is.na(dob)
final_check = is_Z_valid & is_date_valid & is_valid_length & is_status_valid
return(final_check)
}
result = input %>%
mutate(answer_expected = map(sa_i_ds, validate_id)) %>%
filter(answer_expected == TRUE)
&&