Validate South African ID Number with Excel Formula

I have written a single Excel formula to validate South African ID Numbers. I think it is complete. I would welcome input from readers on simplifying. I know there are implementations that exist in Excel but I don’t know of one that uses a single formula in a single cell.

I’ve created an Excel sheet with some made up ID numbers as examples.

These are the checks:

  • If it is not 13 digits long, returns: “Invalid Length”
  • If date digits are > 12, returns: “Invalid Month”
  • Making some assumptions about the year, if the entire date of birth is wrong, return: “Invalid Day”
  • If citizenship bit is not 0 (citizen), 1 (resident) or 2 (refugee), return: “Invalid Citizenship”
  • If the penultimate bit is not 8 or 9, return: “Invalid 2nd Last Digit”
  • Then, the bulk of the formula implements the Luhn algorithm to confirm the check bit. If invalid, returns: “Invalid Check Bit”

Assuming the ID Number is in cell A1, this is the formula from B1 to validate:

=IF(AND(ISNUMBER(INT(A1));LEN(A1)=13);IF(INT(MID(A1;3;2))<13;IF(IF(LEFT(A1;2)>RIGHT(YEAR(TODAY());2);NOT(ISERROR(DATEVALUE(CONCATENATE(1900+INT(LEFT(A1;2));"/";MID(A1;3;2);"/";MID(A1;5;2)))));NOT(ISERROR(DATEVALUE(CONCATENATE(2000+INT(LEFT(A1;2));"/";MID(A1;3;2);"/";MID(A1;5;2))))));IF(INT(MID(A1;11;1))<3;IF(OR(INT(MID(A1;12;1))=8;INT(MID(A1;12;1))=9);IF(INT(MID(A1;13;1))=10-IF(MOD(IF(LEN(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2)=7;(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;6;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;7;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1));IF(LEN(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2)=6;(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;6;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1));(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1))));10)=0;10;MOD(IF(LEN(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2)=7;(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;6;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;7;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1));IF(LEN(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2)=6;(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;6;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1));(MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;1;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;2;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;3;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;4;1)+MID(INT(CONCAT(MID(A1;2;1);MID(A1;4;1);MID(A1;6;1);MID(A1;8;1);MID(A1;10;1);MID(A1;12;1)))*2;5;1))+(MID(A1;1;1)+MID(A1;3;1)+MID(A1;5;1)+MID(A1;7;1)+MID(A1;9;1)+MID(A1;11;1))));10));"Valid";"Invalid Check Bit");"Invalid 2nd Last Digit");"Invalid Citizenship");"Invalid Day");"Invalid Month");"Invalid Length")

Leave a Reply