Skip to content

Instantly share code, notes, and snippets.

@averykhoo
Created December 29, 2018 09:40
Show Gist options
  • Save averykhoo/c6ca9a4170c175518c85fc39df0837ef to your computer and use it in GitHub Desktop.
Save averykhoo/c6ca9a4170c175518c85fc39df0837ef to your computer and use it in GitHub Desktop.
Excel formula to validate Singapore NRICs and FIN numbers (case-insensitive)
= IF(
LEN([NRIC]) = 9,
UPPER(RIGHT([NRIC])) = VLOOKUP(
UPPER(LEFT([NRIC])),
{"S", "J", "Z", "I", "H", "G", "F", "E", "D", "C", "B", "A";
"T", "J", "Z", "I", "H", "G", "F", "E", "D", "C", "B", "A";
"F", "X", "W", "U", "T", "R", "Q", "P", "N", "M", "L", "K";
"G", "X", "W", "U", "T", "R", "Q", "P", "N", "M", "L", "K"},
MOD(VLOOKUP(
UPPER(LEFT([NRIC])),
{"S", 0; "T", 4; "F", 0; "G", 4},
2,
FALSE
) +
SUM(VALUE(
MID([NRIC], {2, 3, 4, 5, 6, 7, 8}, 1)
) * {2, 7, 6, 5, 4, 3 ,2}
),
11
) + 2,
FALSE
),
FALSE
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment