Verify the ID number through the WPS conversion tool

As shown in Figure 1, a is listed as an identity card number (the format of the column cell is set to "text" first before entering), and B is listed as the date of birth in the formula from the ID number, taking B2 as an example, the formula is:

=text (IF (Len (A2) =15, "a",) &mid (A2,7,if (Len (A2) =18,8,6)), "####-##-##"

Using the text function, the formula can intelligently judge 18-bit and 15-digit numbers, and extract the birth date from them.

In Figure 1, we find that due to an input error, a date that is not likely to occur in reality is the result of a "1988-13-1" (B3) and a "2005-02-29" (B5) in the extracted birth date. So, how do we find them quickly?

We copy the date of birth in column B (e.g. B2:B5), then place the cursor in cell C2, click the small arrow to the right of the Paste button, select value from, and copy all dates to column C, at which point the birth date of column C is all selected, and A smart cue symbol appears on the left side of cell C2, with the mouse click on the prompt to select Convert to Number (C) (Figure 2).

In this way, the data in column C is converted from a text format to a real "date" format. However, careful you should have found that only the correct date can be successfully converted, its alignment automatically turned to "right", and the wrong date is not converted, is still text formatting, alignment is still "left", Adjust column C to enlarge, "right right, wrong left" (Figure 3)! All you have to do is proofread the wrong ID numbers and correct them.

After the completion of the above settings, we rely on the date of birth to verify the ID number, easy to find the wrong ID card number, can give users more time to save. There are frequent use of WPS to enter the ID card friends, may wish to make the above settings, to avoid the problem of input errors.