身份證驗證

IF(OR(LEN(G2)=15,IF(LEN(G2)=18,MID("10X98765432",MOD(SUM(MID(G2,ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)=RIGHT(G2))),"√","×")

輸入完公式后按Ctrl+Shift+Enter鍵

IF(OR(LEN(I2)=15,IF(LEN(I2)=18,MID("10X98765432",MOD(SUM(MID(I2,ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)=RIGHT(I2))),"√","×")

輸入完公式后按Ctrl+Shift+Enter鍵

生日提取 TEXT(IF(LEN(H2)=18,MID(H2,7,8),19000000+MID(H2,7,6)),"####-##-##")+0

性別提取 IF(LENB(I2)<18,IF(MOD(RIGHT(I2,1),2),"男","女"),IF(MOD(LEFT(RIGHT(I2,2),1),2),"男","女"))

文本對比  IF(EXACT(C2,D2),"√","×")

日期格式的年月日  轉(zhuǎn)成文本格式的年月日  TEXT(F2,"yyyy.mm.dd")  示例  1900.10.10

文本格式與日期格式對比  IF(EXACT(E2,TEXT(F2,"yyyy.mm.dd")),"√","×")

=LOOKUP(1,0*SEARCH(B2,文件!$A$1:$A$500),文件!$B$1:$B$500)
=COUNTIF(文件!$A$1:$A$500,"*"&B2&"*")  統(tǒng)計 包含B2單元格關(guān)鍵字的次數(shù)

格式化日期 ：數(shù)據(jù)→分列→最后一步的右上角選擇“日期”和YMD選項→完成