身份证

效验

=IF(LEN(A1)=0,"空",IF(LEN(A1)=15,"老号",IF(LEN(A1)<>18,"位数不对",IF(CHOOSE(MOD(SUM(MID(A1,1,1)*7+MID(A1,2,1)*9+MID(A1,3,1)*10+MID(A1,4,1)*5+MID(A1,5,1)*8+MID(A1,6,1)*4+MID(A1,7,1)*2+MID(A1,8,1)*1+MID(A1,9,1)*6+MID(A1,10,1)*3+MID(A1,11,1)*7+MID(A1,12,1)*9+MID(A1,13,1)*10+MID(A1,14,1)*5+MID(A1,15,1)*8+MID(A1,16,1)*4+MID(A1,17,1)*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)= IF(ISNUMBER(RIGHT(A1,1)*1),RIGHT(A1,1)*1,"X"),"正确","错误"))))

15位升18位

=IF(LEN(A2)=15,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),A2)

提取出生年月

=IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,2),"/",MID(A1,9,2),"/",MID(A1,11,2)),CONCATENATE(MID(A1,7,4),"/",MID(A1,11,2),"/",MID(A1,13,2)))

计算年龄

=INT((TODAY()-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))/365)

=TEXT(DAYS(TODAY(),A1),"y")

判断性别

=IF(MOD(MID(A1,17,1),2)=1,"男","女")

统计

统计单元格内的某字符串出现次数

=(LEN(A1)-LEN(SUBSTITUTE(A1,"1","")))/LEN("1")

日期转换

中文日期转数字时期

=--(SUM((MATCH(MID($A1,{1,2,3,4},1),TEXT(ROW(INDIRECT("1:10"))-1,"[dbnum1]"),0)-1)*10^{3,2,1,0})&TEXT(MATCH(SUBSTITUTE(MID($A1,6,7),"元","一"),TEXT(ROW(INDIRECT("1:366")),"[dbnum1]m月d日"),0),"-m-d"))