Excel converts 15-bit ID numbers to 18-bit

Suppose A is a A2 ID number (15-bit or 18-bit) from the beginning of the list.

1, the ID number is changed to 18 digits, input array formula: =if (LEN (A2) =15,replace (a2,7,,19) &mid ("10x98765432", MOD (SUM () (REPLACE (a2,7,,19)

, ROW (INDIRECT ("1:17")), 1 *2^ (18-row (INDIRECT ("1:17"))), +1,1), A2)

Note: Array formula Input Method: Do not press ENTER after entering the formula, but press Ctrl+shift+enter.

2, the identity card number is changed to 15 digits, enter the formula: =if (LEN (A2) =15,a2,left (REPLACE (a2,7,2,), 15))

3. Calculated date of birth: =if (A2 "", TEXT (Len (A2) =15) *19&mid (a2,7,6+ (Len (A2) =18) *2), "#-00-00") +0,)

4. Determine Gender: =if (A2 "", IF (MOD (left (a2,17), 2), "Male", "female"),)

The final result is as shown in figure: