身份证号码信息提取较验公式

tjstar

提取性别：=IF(B2<>"",IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女"),)

提取生日：=IF(B2<>"",TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),"#-00-00")+0,)

提取年龄：=IF(B2<>"",DATEDIF(TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),"#-00-00"),TODAY(),"y"),)

提取退休年龄：=IF(B2<>"",EDATE(TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),"#-00-00"),600+MOD(RIGHT(LEFT(B2,17)),2)*120),)

改成18位：=IF(LEN(B2)=15,REPLACE(B2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),B2)

改成15位：=IF(LEN(B2)=15,B2,LEFT(REPLACE(B2,7,2,),15))