如何使用Excel從身份證號(hào)碼中提取出生日期及性別

1、如何使用 Excel 從身份證號(hào)碼中提取出生日期 2009-02-27 22:52 例如：從身份證中提取出生日期來(lái)，如何快速得出？呵呵， 只需使用語(yǔ)句： =DATE(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2)【 A1 是身份證號(hào)碼所在單元格】date() 函數(shù)，地球人都知道，日期函數(shù)；如輸入今天的日期=today()那么， mid 函數(shù)是什么東東呢？MID(text,start_num,num_chars)Text 為包含要提取字符的文本字符串； Start_num 為文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的 s

2、tart_num 為 1 ，以此類推； Num_chars 指定希望 MID 從文本中返回字符的個(gè)數(shù)。對(duì)身份證號(hào)碼分析下就知道：，出生日期是 1992 年 2 月 6 日；也就是從字符串()的第 7 位開(kāi)始的 4 位數(shù)字表示年，從字符串的第 11 位開(kāi)始的 2 位數(shù) 字表示月，字符串的第 13 位開(kāi)始的 2 位數(shù)字表示日。呵呵，強(qiáng)悍吧！Excel 中利用身份證號(hào)碼( 15 或 18 位)提取出生日期和性別需要的函數(shù)：LEN(C6)=15 ：檢查 C6 單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號(hào)碼的長(zhǎng)度是否是

3、 15 位；INT: 返回?cái)?shù)值向下取整為最接近的整數(shù) ,本例中用來(lái)判斷身份證里數(shù)值的奇偶數(shù)。RIGHT: 返回文本字符串最后一個(gè)字符開(kāi)始指定個(gè)數(shù)的字符；MID ：返回文本字符串指定起始位置起指定長(zhǎng)度的字符，MID(C6,7,2) 表示：在 C3 中從左邊第七位起提取2 位數(shù)；"19"&MID(C6,7,2) 表示：在 C3 中從左邊第七位起提取 2 位數(shù)的前面添加 19 ；&""&表示：其左右兩邊所提取出來(lái)的數(shù)字不用任何符號(hào)連接；&"-"& 表示：其左右兩邊所提取出來(lái)的數(shù)字間用“-”符號(hào)連接。若

4、需要的日期格式是yyyy 年 mm 月 dd 日，則可以把公式中的 “- ”分別用 “年月日 ”進(jìn)行替換就行了。、提取出生日期如果我們要從一個(gè)人的身份證號(hào)碼中批量提取其出生年月日，并表示成“yyyymm- dd”形式，可以這樣做，假設(shè)身份證號(hào)碼在 C 列，在 D 列中輸入公式=IF(LEN(C6)=15,"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2),MID(C6,7,4)&"-"&MID(C6,11,2)&

5、amp;"-"&MID(C6,13,2)，這個(gè)公式的含義就是，當(dāng)其檢查到C6單元格中的數(shù)據(jù)是15位的時(shí)，就顯示"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2) 的計(jì)算結(jié)果，否則就顯示MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2) 的計(jì)算結(jié)果。如：若 C6 單元格中是 441000500112418 ， 在

6、D6單元格中計(jì)算出的結(jié)果是 “195001-12” ；若C6單元格中是，在D6單元格中 計(jì)算出的結(jié)果是 “1945-11-16”。二、提取性別在 E6 單元格輸入公式 =IF(LEN(C6)=15,IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),"女","男"),IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2)," 女","男")這個(gè)公式的含義是如果 C6 單元格是一個(gè) 15 位數(shù)， 就顯示 IF(RIGHT(C6)/2=INT(RI

7、GHT(C6)/2)," 女","男")的計(jì)算結(jié)果；否則，顯示IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2)," 女","男")的計(jì)算結(jié)果。最后把 D6 和 E6 單元格的公式向下拉下來(lái)，在 C6 列輸入身份證號(hào)碼后，出生日期和性別可自動(dòng)輸入了， 這樣就減少了用戶輸入數(shù)據(jù)工作量，提高了辦事效率！根據(jù)身份證號(hào)碼讓 Excel 自動(dòng)輸入出生日期和性別辦公室人事文員有時(shí)要輸入很多員工的人事資料， 每輸完臃長(zhǎng)的身份證號(hào)后又要輸入員工出生年月日和性別，這樣無(wú)疑增加了工作量，有沒(méi)有辦法讓出生日期和性

8、別自動(dòng)輸入呢？其實(shí)用Excel 公式即可輕松解決問(wèn)題！眾所周知，我國(guó)身份證號(hào)碼里有每個(gè)人的出生日期和性別等信息：老式的身份證號(hào)是15 位數(shù)，第 7位到 12 位是出生年月日， 最后一位如果是偶數(shù)就代表女生， 奇數(shù)就是代表男性； 新式身份證號(hào)是 18 位數(shù)， 第7 位到 14 位是出生日期，倒數(shù)第二位的偶數(shù)或奇數(shù)代表女性或男性。既然存在這樣的規(guī)律，用Excel函數(shù)就可輕松實(shí)現(xiàn)根據(jù)身份證號(hào)自動(dòng)輸入出生日期和性別。本例中所需要函數(shù) :LEN: 返回文本字符串的個(gè)數(shù) ,本例中用來(lái)計(jì)算身份證的位數(shù)MID: 返回文本字符串指定起始位置起指定長(zhǎng)度的字符,本例中用來(lái)計(jì)算身份證號(hào)中出生日期的字符 ;INT: 返

9、回?cái)?shù)值向下取整為最接近的整數(shù) ,本例中用來(lái)判斷身份證里數(shù) 值的奇偶數(shù) .RIGHT: 返回文本字符串最后一個(gè)字符開(kāi)始指定個(gè)數(shù)的字符,本例中來(lái)計(jì)算身份證號(hào)最后一位數(shù) .如果A3單元格是身份證號(hào)碼,B3單元格為出生日期，C3單元格 為性別,那么在 B3 中輸入公式 =IF(LEN(A3)=18,MID(A3,7,4)&" 年 "&MID(A3,11,2)&"月"&MID(A3,13,2)&"日","19"&MID(A3,7,2)&"年 "&am

10、p;MID(A3,9,2)&"月"&MID(A3,11,2)&"日"),這個(gè)公式的含義是，如果 A3 單元格是一個(gè) 18位數(shù) IF(LEN(A3)=18) 那么從第 7位數(shù)開(kāi)始得到 的 4 位數(shù)就是年份 MID(A3,7,4) 后面再加上文字 &”年”月,日依此類推 , 否則,A3單元格為15位數(shù),就執(zhí)行"19"&MID(A3,7,2)&"年"&MID(A3,9,2)&"月"&MID(A3,11,2)&"日

11、”)含義與 18 位相似.只是在 年 MID(A3,7,2) 前面要加上 19在 C3 單元格輸入公式 =IF(LEN(A3)=18,IF(MID(A3,17,1)/2=INT(MID(A3,17,1)/2),"女","男"),IF(RIGHT(A3)/2=INT(RIGHT(A3)/2)," 女","男")這個(gè)公式的含義是 如果 A3 單元格是一個(gè) 18 位數(shù) IF(LEN(A3)=18) 就執(zhí)行IF(MID(A3,17,1)/2=INT(MID(A3,17,1)/2)," 女","

12、男"),其中如果 A3 單 元格第 17 位數(shù)(身份證號(hào)倒數(shù)第二位 )除以二 MID(A3,17,1)/2 等于一 個(gè)整數(shù) INT(MID(A3,17,1)/2), 那么第 17 位為偶數(shù),即是”女”否,則是奇 數(shù)，即為 ”男”否;則 A3 單元格是 15 位數(shù)，就執(zhí)行IF(RIGHT(A3)/2=INT(RIGHT(A3)/2)," 女","男"),其中 RIGHT(A3) 含義 是返回 A3 單元格 從右往左的第一位數(shù) ,即身份證最后一位數(shù) .其余含意跟上面 18 位數(shù)一樣 .最后把 B3 和 C3 單元格的公式向下拉下來(lái)，在 A3 列輸入身份證號(hào)碼后，出生日期和性別可自動(dòng)輸 入了，這樣就減少了用戶輸入