如何在EXCEL中批量将15位身份证号码变更为18位身份证号码?

Question

请教，如何在EXCEL中批量将15位身份证号码变更为18位身份证号码？

要对一千多人的身份证号码，无限头疼啊？

Answer 1

首先需要知道身份证号中各个数字的含义，以18位身份证号主讲，

前边6个数字，每2个数字一组，分别是省（自治区、直辖市）、市（盟、自治州）、县（县级市、区）的代码，

接下来的8位数字（15位身份证是6位数字）是出生年月日（15位身份证号的年是两位数，省略了19），比如1975年6月5日出生的，这数字就是19750605（15位身份证中就是750605），

再往后的3个数是顺序码，这3个数的最后一个数，奇数为男，偶数为女。


最后1位是效验码（15位身份证号没有，就是为了防止身份证重号，而增设的）

总结：15位身份证号与18位身份证号的区别：

    区别一：年份少个“19”，

    区别二：最后少一个验证码。加“19”很容易，关键就在最后一个效验码的计算。

效验码是根据ISO 7604：1983.MOD11-2计算的，具体的计算规则看下图

至此可以写出身份证号升级公式了=REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)

上边公式中没有直接引用“加权因子”，而是使用了“2^(18-ROW($1:$17))”，用这个去乘身份证的每一个数，再求余数，结果是一样的，自己可以验证下。

Answer 2

举例说明。

例如有数据表如图：

在D2输入公式：=CONCATENATE(LEFT(B2,6),19,RIGHT(B2,9),"X"),并将公式向下拖动至C4。结果如图：

Answer 3

18位身份证最后一位是校验位, 不那么简单.

15位的老身份证号码插入年份即可升到17位:

例如 B2 单元格中是这个17位的号码:
=IF(LEN(A2)=15,MID(A2,1,6)&"19"&MID(A2,7,9))

那么在 C2 中输入数组公式(公式输入后须同时按下 Ctrl+Shift+Enter 组合键方可完成):
=B2&LOOKUP(MOD(SUM(MID(B2,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2})
即可得到18位(上述公式为函数大师 阵军 的作品).

参考资料:

公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。

校验方法：
（1）十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, … , 16 ，先对前17位数字的权求和
Ai:表示第i位置上的身份证号码数字值
Wi:表示第i位置上的加权因子
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

（2）计算模
Y = mod(S, 11)

（3）通过模得到对应的校验码

Y: 0 1 2 3 4 5 6 7 8 9 10
校验码: 1 0 X 9 8 7 6 5 4 3 2

Answer 4

通过身份证联网核查系统修改，里面有自动升位功能。不过只有银行或者公安局里面才有。

Answer 5

可以写一段VBA程序自动完成