2024年4月12日发(作者：)

(完整word版)如何用Excel验证身份证号码是否符合规则

EXCEL中如何提取身份证出生日期、性别、检验身份证号码的正确性

中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为:

１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。

１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码,３位数字顺序码和１位检验

码.

其中３位数字顺序码,是为同一地址码的同年同月同日出生人员编制的顺序号,偶数的为女性，奇数的为男

性。

１、提取籍贯地区的行政区划代码（A2为身份证号,下同)

１５与１８位通用：=LEFT（A2，6）

如果有一个编码和省份地区的对照表，可以用VLOOKUP函数来提取地区信息。

２、提取出生日期信息

１５位：=--TEXT（19&MID(A2,7,6）,"#—00—00”)

１８位:=——TEXT(MID(A2，7，8），”#—00-00"）

１５与１８位通用：=--TEXT（IF（LEN(A2）=15,19，”"）&MID（A2,7，6+IF(LEN(A2)=18,2，

0))，”#-00—00”)

简化公式：=-—TEXT((LEN(A2）=15）＊19＆MID(A2,7,6+(LEN(A2)=18）*2），”＃-00-00”)

(完整word版)如何用Excel验证身份证号码是否符合规则

（请将输入公式的单元格格式设置为日期格式）

３、提取性别信息

１５位：=IF(MOD（RIGHT(A2）,2)=1，”男","女")

１８位:=IF（MOD(MID(A2），17,1)=1，”男”,"女"）

１５与１８位通用:=IF（MOD(MID（A2，IF(LEN（A2)=15,15,17)，1),2）=1，"男",”女"）

简化公式：=IF(MOD(RIGHT(LEFT（A2，17))，2),”男”，"女”）

４、检验身份证号码的正确性

１８位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０~９或

X.一般情况只要有一位数字输入错误，依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰

好与检验码相符的情况,但这种情况出现的可能性较低。因此，对１８位号码的验证采用如下公式：

=MID（"10X98765432”，MOD(SUMPRODUCT（MID（A2,ROW（INDIRECT("1：17”)),1)*2^（18-ROW

（INDIRECT（”1：17"))）),11）+1,1)=RIGHT（A2,1)

对于１５位身份证，由于没有检验码,我们只能简单地去判断出生日期代码是否是一个有效的日期，避免输

入一些像“731302”或“980230“等这样不存在的日期。

=ISNUMBER（--TEXT（19&MID（A2,7,6），"#—00—00"))

综合１５位和１８位后的通用公式为：