Excel中輸入身份證號碼及提取信息

  一、解決輸入身份證號碼的問題

默認情況下在Excel當前單元格中輸入的數字位數如果超過11位(不含11位)時,系統將以「科學記數」格式顯示輸入的數字;當輸入的數字位數超過15位(不含15位)時,系統將15位以後的數字全部顯示為「0」。這樣一來,如果我們要輸入身份證號碼(15位或18位),身份證號碼就不能正確顯示出來了。但我們有兩種辦法解決此問題:

1、利用數字標籤

選中需要輸入身份證號碼的單元格(或區域),打開「單元格格式」對話框,進入「數字」標籤,在「分類」下面選中「文本」選項,確定退出即可。

2、在輸入身份證號碼時,在號碼前面加上一個英文單引號「’」,即可讓身份證號碼完全顯示出來(該單引號不顯示)。

二、讓單元格內自動添加身份證前面相同部分

身份證號碼的前6位,是省、市、區(縣)的代號(如資中縣:511025),如果持證人都是資中縣的,輸入這樣的的身份證號碼,可以讓單元格格式的「數字」標籤幫你減輕輸入的工作量:選中需要輸入身份證號碼的單元格區域,單擊Excel上面菜單欄的格式打開「單元格」對話框(或者右鍵點擊設置單元格格式),進入默認的「數字」標籤,在「分類」下面選中最下面的「自定義」選項,然後在右側「類型」下面的方框中輸入:”511025″@,確定。以後輸入身份證號碼9位及以後的數字(如「197303246211」)後,系統會自動在前面加上51102519,形成完整的身份證號碼(如「511025197303246211」)。

其它需要輸入數字含相同部分的也可按照這個方法輸入。

Excel從身份證號碼中提取個人信息。

如18位身份證號碼:前6位是公民戶口登記地,第7到14位代表出生日期,第17位代表性別,奇數為男,偶數為女。 (15位身份證號碼15位數代表性別)

比如,某員工的身份證號碼(18位)是320521197208170241,那麼表示1972年8月17日出生,性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來,不僅快速簡便,而且不容易出錯。

提取個人信息 需要使用IF、LEN、MOD、 MID、DATE等函數。某表格,其中員工的身份證號碼信息已輸入到C列,出生年月信息填寫在D列,性別信息填寫在B列。

1. 提取出生年月信息

由於上交報表時只需要填寫出生年月,不需要填寫出生日期,因此這裡我們只需要關心身份證號碼的相應部位即可,即顯示為「7208」這樣的信息。在D2單元格中輸入公式「=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))」,其中:

LEN(C2)=15:檢查C2單元格中字符串的字符數目是否等於15,意即檢查身份證號碼的長度是否是15位。

MID(C2,7,4):從C2單元格中字符串的第7位開始提取四位數字,即提取15位身份證號碼的第7、8、9、10位數字。

MID(C2,9,4):從C2單元格中字符串的第9位開始提取四位數字,即提取18位身份證號碼的第9、10、11、12位數字。

IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一個邏輯判斷函數,表示如果C2單元格是15位,則提取第7位開始的四位數字,否則就從第9位開始提取四位數字。

如果需要顯示為「70年12月」這樣的格式,請使用DATE格式,並在「單元格格式→日期」中進行設置。

2. 提取性別信息

由於報表中各位員工的序號編排是按照上級核定的編制進行的,因此不可能按照男、女固定的順序進行編排,如果一個一個手工輸入的話,既麻煩又容易出錯

例如性別信息統一在B列填寫,可以在B2單元格中輸入公式「=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男”,”女”)」,其中:

LEN(C2)=15:檢查身份證號碼的長度是否是15位。

MID(C2,15,1):如果身份證號碼的長度是15位,那麼提取第15位的數字。

MID(C2,17,1):如果身份證號碼的長度不是15位,即18位身份證號碼,那麼應該提取第17位的數字。

MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2):用於得到給出數字除以指定數字後的餘數,本例表示對提出來的數值除以2以後所得到的餘數。

IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男”,”女”):如果除以2以後的餘數是1,那麼B2單元格顯示為「男」,否則顯示為「女」。

回車確認後,即可在B2單元格顯示正確的性別信息,接下來就是選中填充柄直接拖曳。如圖2所示,現在這份報表無論是提取信息或是核對,都方便多了!