图例
1、身份证号:以上图为例 ,在d2中输入
=DATE(MID(B3,7,IF(LEN(B3)=18,4,2)),MID(B3,IF(LEN(B3)=18,11,9),2),MID(B3,IF(LEN(B3)=18,13,11),2)),则 显示
1965/8/15 |
2、提取性别 c2中输入, =IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女")
或=IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女")
显示 女
3、15位改18位 l2中输入
=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)
显示是:
110221196508152245 |
4、提取现在年龄:
=IF(B2<>"",DATEDIF(TEXT((LEN(B2)=15)*19&MID(B2,7,6 (LEN(B2)=18)*2),"#-00-00"),TODAY(),"y"),)
显示: 52
5提取生肖
=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(D2)-4,12) 1,1) 显示“蛇”
6 号码正确否
=OR(LEN(B2)=15,IF(LEN(B2)=18,MID("10X98765432",MOD(SUM(MID(B2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11) 1,1)=RIGHT(B2))) 显示true
7、提取所属省份
我觉得很全了,如果喜欢请加关注
,