在我們平時的工作生活中經常會接觸到身份證號,如果現在有很多人的身份證號,要統計這些人的出生日期,如何自動批量從很多人的身份證號中提取出來出生日期呢?下面我們以15個人的身份證號為例,批量提取他們的出生日期:
在C2單元格中輸入公式:=TEXT(MID(B2,7,8),"0000-00-00")
這個公式的含義是,從B2單元格中的從左向右數第7個字符開始提取出來8個字符,因為身份證號中代表出生年月日的就是身份證號的第7位開始向右的8個數字。提取出來後放到TEXT文本函數中轉換成特定格式的文本,這裡的0000-00-00就是用橫槓分隔取出的8個數字。
回車後:
這樣就提取出來了B2單元格的出生年月日,然後向下填充公式即可。
這樣所有人的身份證號中的出生年月日都被提取出來了。是不是很省事呢?
如果公式換成這樣:=TEXT(MID(B2,7,8),"0000年00月00日") 會得出什麼結果呢?請大家自己試試吧。
注意:
1. 以上方法提取出來的是文本格式的日期,如果要提取出來是數字格式的日期,可以在公式中的TEXT前面加上兩個負號轉換即可,例如:=--(TEXT(MID(B2,7,8),"0000-00-00"))
回車後:
然後將公式向下填充得到:
怎麼全是數字?不要急,其實這些數字本質上是日期,選中這些數字,點擊鼠標右鍵,選擇【設置單元格格式】==》【數字】==》【日期】選擇一種日期格式即可。
這樣就現出日期的原形了。
(2)如果一個日期是數字格式,如下圖中的A1單元格中是數字26639,如何轉換成日期格式呢?只需在B1單元箇中輸入:=TEXT(A1,"yyyy年m月d日")
回車後:
這樣就轉換成日期格式了。
補充知識:
(1)Text函數:其功能是將數值轉化為自己想要的文本格式
Text函數語法:
TEXT(value, format_text)
TEXT 函數語法具有下列參數:
value :數值、計算結果為數值的公式,或對包含數值的單元格的引用。
format_text:用引號括起的文本字符串的數字格式。例如,“m/d/yyyy”或“#,##0.00”。
(2)MID函數:用於從字符串的指定位置開始,提取特定數目的字符串。
MID函數語法:
MID(text,start_num,num_chars)
MID函數語法具有下列參數:
text表示指定的字符串,一般為引用的單元格;
start_num表示指定位置 ,即表示要從字符串的由左向右哪一位開始。
num_chars表示指定個數,即要提取多少個字符。
MID(A1,5,3)就是從A1單元格的第5位開始,提取3個字符。
以上囉嗦了一大堆,不知道大家學到點知識沒,反正我是學到了,哈哈!