EXCEL中判断身份证号是否正确的方法探讨
身份证有15位和18位两种，身份证位数是否正确，我们可以用LEN函数判断。但身份证上的日期是否合法：月份是否在1-12之间，日期是否在1-31之间，并且2月份只有28或29天，其他月份30或31天，都不能超过范围。另外一般规定6岁以上才可以办理身份证，也就是年份也有一个超范围的可能性。综合起来看，有三类错误：“身份证位数不对”、“月日错误”、“年份错误”。
mid函数提取年月日假定身份证号码在B1单元格，下面的公式可以综合判断以上三种错误：
=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MONTH(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,11,2)),DAY(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,13,2))),"月日错误",""),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,9,2)),DAY(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,11,2))),"月日错误",""))),"身份证位数不对")&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),"年份错误","")
公式首先对身份证长度进行判断，是否等于15位或18位，然后对18位和15位身份证分别判断月日是否合法，最后判断18位身份证的年份是否合法。这里的关键是如何判断日期是否合法。现将判断方法介绍如下：
先用MID函数取得身份证中的年月日，用乘1的方法转换为数值，用DATE函数转换成一个日期。这一转换过程中，超范围的月份和日期同样可以正确转换，比如月份数为15，转换后月份数为3，而年份数已经加上1，又如19630835转换后就成了1963年9月4日。这时我们用MONTH、DAY两个函数从转换后的日期中分别取出月日与身份证中原有的月日比较，只要有一项不符该日期就是不合法。
该判断方法不错，缺点是公式太长。
欢迎各位大侠批评指正。
根据网友的提供资料，现有一种更加简单的判断公式如下：
=IF(ISERROR(1*TEXT((LEN(b1)=15)*19&MID(b1,7,6+(LEN(b1)=18)*2),"#-00-00")),"错误","")
补充:预防身份证号码输入时长度和日期错误的方法:
选中B列,有"数据"→"有效性"→"设置"→"允许"选择"自定义"→输入下面的公式
=NOT(OR(ISERR(--TEXT(MID(B1,7,LEN(B1)/2-1),"#-00-00")),AND(LEN(B1)<>15,LEN(B1)<>18)))
点击"确定"即可。
身份证判断男女
=IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)=1,"男","女")
身份证提取日期
=IF(LEN(C3)=18,MID(C3,7,4)&"-"&MID(C3,11,2)&"-"&MID(C3,13,2),"19"&MID(C3,7,2)&"-"&MID(C3,9,2)&"-"&MID(C3,11,2))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。