今天跟大家分享一個壓箱底的技能:在excel中快速提取身份證中的生日及性別。
話不多說,先上乾貨!
我們要想提取身份證中的信息首先就要先了解18位身份證的含義:
(1)前1-2位數字表示:所在省份的代碼;
(2)第3-4位數字表示:所在城市的代碼;
(3)第5-6位數字表示:所在區縣的代碼;
(4)第7-14位數字表示:出生年、月、日;
(5)第15-16位數字表示:所在地的派出所的代碼;
(6)第17位數字表示性別:奇數表示男性,偶數表示女性;
(7)第18位數字是校檢碼
我們已經知道第7-14位代表出生年月日,我們需要用到MID函數,首先要將D列設置為日期格式,利用公式:=DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2)) 提取身份證中的出生日期。
動圖如下:
今天跟大家分享一個壓箱底的技能:在excel中快速提取身份證中的生日及性別。
話不多說,先上乾貨!
我們要想提取身份證中的信息首先就要先了解18位身份證的含義:
(1)前1-2位數字表示:所在省份的代碼;
(2)第3-4位數字表示:所在城市的代碼;
(3)第5-6位數字表示:所在區縣的代碼;
(4)第7-14位數字表示:出生年、月、日;
(5)第15-16位數字表示:所在地的派出所的代碼;
(6)第17位數字表示性別:奇數表示男性,偶數表示女性;
(7)第18位數字是校檢碼
我們已經知道第7-14位代表出生年月日,我們需要用到MID函數,首先要將D列設置為日期格式,利用公式:=DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2)) 提取身份證中的出生日期。
動圖如下:
雙子幫大家分解一下這個公式,MID(C4,7,4)說的是截取字段,從C4單元格的第7位開始截取,共截取4位字符,也就是1995,即年份,同理MID(C4,11,2)和MID(C4,13,2)分別截取的是月份和日期,怎麼樣,理解了嗎?
下面我們接著提取身份證中的性別。
最開始我們有提到身份證中第17位數字代表性別:奇數表示男性,偶數表示女性;
利用性別提取公式:=IF(MOD(MID(C4,17,1),2),"男","女")
動圖操作如下:
今天跟大家分享一個壓箱底的技能:在excel中快速提取身份證中的生日及性別。
話不多說,先上乾貨!
我們要想提取身份證中的信息首先就要先了解18位身份證的含義:
(1)前1-2位數字表示:所在省份的代碼;
(2)第3-4位數字表示:所在城市的代碼;
(3)第5-6位數字表示:所在區縣的代碼;
(4)第7-14位數字表示:出生年、月、日;
(5)第15-16位數字表示:所在地的派出所的代碼;
(6)第17位數字表示性別:奇數表示男性,偶數表示女性;
(7)第18位數字是校檢碼
我們已經知道第7-14位代表出生年月日,我們需要用到MID函數,首先要將D列設置為日期格式,利用公式:=DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2)) 提取身份證中的出生日期。
動圖如下:
雙子幫大家分解一下這個公式,MID(C4,7,4)說的是截取字段,從C4單元格的第7位開始截取,共截取4位字符,也就是1995,即年份,同理MID(C4,11,2)和MID(C4,13,2)分別截取的是月份和日期,怎麼樣,理解了嗎?
下面我們接著提取身份證中的性別。
最開始我們有提到身份證中第17位數字代表性別:奇數表示男性,偶數表示女性;
利用性別提取公式:=IF(MOD(MID(C4,17,1),2),"男","女")
動圖操作如下:
很多公司在員工生日的時候會給大家送祝福,想知道怎麼設置生日提醒嗎?上週我們有講到設置合同到期提醒的方式【點擊這裡查看】,今天我們利用一個新的公式:=TEXT(TEXT(D4,"m-d")-TEXT(TODAY(),"m-d"),"還有0天過生日;生日已過;祝你生日快樂")來設置生日提醒。
動圖如下:
今天跟大家分享一個壓箱底的技能:在excel中快速提取身份證中的生日及性別。
話不多說,先上乾貨!
我們要想提取身份證中的信息首先就要先了解18位身份證的含義:
(1)前1-2位數字表示:所在省份的代碼;
(2)第3-4位數字表示:所在城市的代碼;
(3)第5-6位數字表示:所在區縣的代碼;
(4)第7-14位數字表示:出生年、月、日;
(5)第15-16位數字表示:所在地的派出所的代碼;
(6)第17位數字表示性別:奇數表示男性,偶數表示女性;
(7)第18位數字是校檢碼
我們已經知道第7-14位代表出生年月日,我們需要用到MID函數,首先要將D列設置為日期格式,利用公式:=DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2)) 提取身份證中的出生日期。
動圖如下:
雙子幫大家分解一下這個公式,MID(C4,7,4)說的是截取字段,從C4單元格的第7位開始截取,共截取4位字符,也就是1995,即年份,同理MID(C4,11,2)和MID(C4,13,2)分別截取的是月份和日期,怎麼樣,理解了嗎?
下面我們接著提取身份證中的性別。
最開始我們有提到身份證中第17位數字代表性別:奇數表示男性,偶數表示女性;
利用性別提取公式:=IF(MOD(MID(C4,17,1),2),"男","女")
動圖操作如下:
很多公司在員工生日的時候會給大家送祝福,想知道怎麼設置生日提醒嗎?上週我們有講到設置合同到期提醒的方式【點擊這裡查看】,今天我們利用一個新的公式:=TEXT(TEXT(D4,"m-d")-TEXT(TODAY(),"m-d"),"還有0天過生日;生日已過;祝你生日快樂")來設置生日提醒。
動圖如下:
好了,今天的技能分享就是這些,各位小可愛快快收藏學習,走上人生巔峰指日可待了。