Quickly find the wrong ID information in Excel

As office staff, often to write about the staff information file form, the most often entered the data is the ID card number, due to the number of people, the importation of the real trouble, inadvertently may be the employee's birth date is wrong, this is related to each employee's vital interests, such as seniority, social insurance and so on It's a pain in the neck to prove it. The following I teach you a trick, through this method can quickly find the Excel table in the wrong ID number. The following is an example of "XXX company employee Information statistics".

First, from the ID number of column B (note: The column cell format must be unified to "text") to extract the date of birth to column C, for example, select C3 cell, and then in the formula bar, enter the following equation: =text (IF (LEN (B3) =15, "C3") &mid (B3,7,if (LEN (B3) =18,8,6)), "####-##-##", which uses a text function that intelligently judges 18-bit and 15-bit numbers and extracts birth dates from them.

After all the extraction, you will find that the date of birth unexpectedly appeared "1980-13-15" (C4), who can be born on such a date? Obviously this is the wrong message.

The next thing we need to do is to quickly find all the wrong information. Select all C-column Birth date c3:c4 and click the Copy button, and then position the cursor in the D column cell, click the small arrow to the right of the Paste button in the toolbar, and choose the value option.

After you copy all the dates to column D, the date of birth for column D is all selected, and a smart prompt appears on the left side of the D3 cell, clicking the small arrow on the right side of the prompt and selecting the Convert to Number (C) option.

The data in the D column is converted from the text format to the real date format. Careful you will find that only the correct date can be converted successfully, its alignment is "right", and the incorrect date is still text format, alignment is still "left-aligned." With comparison, we can see the wrong ID number at a glance, which is quite easy to correct.

Small hint: At present our country's ID card number divides into two kinds, one kind is 15 digits, one kind is 18 bits. Of the 15-bit old ID card, the 7th to 12th is the date of birth, and the 7th to 14th digit of the 18-digit new identity card is the date of birth.