Excel提取身份證出生日期②
- 2020 年 8 月 20 日
- 筆記
問題場景
-
從user表中的身份信息中提取用戶的出生日期；
-
以下方法也可適用於提取其他數據，不僅是身份證信息；
-
以下圖中數據都為測試數據，不具備真實性！
場景一
-
user表中的18位身份證，提取出生日期。
-
- 不考慮15位身份證號，場景二會提及15位時的情況。
目標
- 提取身份證信息列所有用戶的出生日期。
解決方案一
用Ctrl+E提取填充。
-
第一步：將D2單元格【身份證信息列】的7-14位（出生日期）複製到F2單元格。
-
- 18位身份證信息的7-14位是出生日期。
-
第二步：同時按Ctrl+E填充，最後結果。
-
- 注意：這種方式提取出的不是日期格式。
解決方案二
用mid()函數和連接符號「&」提取
- 函數解釋
=MID(text, start_num, num_chars)
#從一個字符串中截取出指定數量的字符
#text被截取的字符，start_num從左起第幾位開始，num_chars截取的長度是多少
-
第一步：在F2單元格英文狀態下輸入:=MID(D2,7,4)&”-“&MID(D2,11,2)&”-“&MID(D2,13,2)。
-
- 公式解釋：MID(D2,7,4）、MID(D2,11,2)、MID(D2,13,2)分別截取的出生年、出生月、出生日，再用&符號將年月日用「-」連接起來（用”/”連接年月日也可以）。
- 第二步：鼠標移到F2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第三步：最後結果。
解決方案三
用data()函數和mid()函數提取。
- 函數解釋
=DATE(year,month,day)
#將結果將設為日期格式，且year,month,day三個參數為年、月、日參數
#MID函數解釋看上面方案二
-
第一步：在F2單元格英文狀態下輸入：=DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2))。
-
- 公式解釋：MID(D2,7,4）把D2單元格的數據從第7位開始，截取4位，是出生的年份，對應DATE()函數里的第一個參數：year，另外兩個參數同理。
- 第二步：鼠標移到F2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第三步：最後結果為日期格式。
解決方案四
用Text()函數和mid()函數提取。
- 函數解釋
=TEXT(value,format_text)
#通過格式代碼向數字應用格式，進而更改數字的顯示方式
#value為引用的單元格數值，format_text為單元格格式參數代碼，這裡只用一種"00-00-00",表示日期
-
第一步：在F2單元格英文狀態下輸入：=TEXT(MID(D2,7,8),”00-00-00″)。
-
- 公式解釋：MID(D2,7,4）把D2單元格的數據從第7位開始，截取8位，是出生的日期，再用text()函數用日期方式顯示。
- 第二步：鼠標移到F2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第三步：最後結果。
場景二
-
user表中的同時存在18位或15位身份證，提取出生日期。
-
- 由於15位身份證號出生年只有2位，是7-8位，考慮到千年的因素（1900-1999,2000-2020（至今）），得先確定年份都是19開頭還是20開頭。假如19開頭：
-
- 該情況不常見，下面不詳細解釋函數。
目標
- 提取身份證信息列所有用戶的出生日期。
解決方案一
用Text()、Len()和Mid()函數。
=TEXT((LEN(D2)=15)*19&MID(D2,7,6+(LEN(D2)=18)*2),"00-00-00")
- 第一步：在F2單元格中輸入上述公式，鼠標移到F2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第二步：最後結果（紅框里的兩條數據是特例，根據常識很大可能是20開頭的年份）。
解決方案二
用IF()、Len()和Mid()函數。
=IF(LEN(D2)=15,"19"&MID(D2,7,2)&"-"&MID(D2,9,2)&"-"&MID(D2,11,2),MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2))
- 第一步：在F2單元格中輸入上述公式，鼠標移到F2單元格的右下角，出現實心的黑色【+】號，雙擊填充此公式。
- 第二步：最後結果（紅框里的兩條數據是特例，根據常識很大可能是20開頭的年份）。
解決方案三
-
len()函數篩選出身份證為15位和18位的，分別提取。
-
15位提取方法同18位提取方法原理一樣。
總結
實際業務場景複雜時，如果不能一次性解決問題，可擇優使用、或可結合使用，多嘗試，辦法總比困難多！
