Based on this feature, we can use functions to judge. As shown in Figure 1, we have entered the employee information table with the ID number. When entering the ID number, you need to pay attention to setting the cell format to text, or adding a single quotation mark "'" before the entered ID number. .

1. Extract date of birth

Enter the formula "=IF(LEN(C2)=15,TEXT(MID(C2,7,6),"October 00, 1900"),TEXT(MID(C2,7,8),"00) in D2 Year 00 month 00 "))", drag the fill handle downwards to copy the formula, thus completing the extraction of the date of birth (Figure 2):

2. Extract gender information

Enter the formula "=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"Male","Female ")", drag the fill handle downward to copy the formula, thus completing the extraction of gender information (Figure 3):

Detailed explanation of the formula: LEN is the length function, MID is the character extraction function, TEXT is the conversion format function, MOD is the integer division function, and IF is the judgment function.

The formula for extracting the date of birth means that if the ID number is 15 digits, convert the 6 characters starting from the 7th digit into the format of "October 00, 1900", otherwise (that is, an 18-digit ID card), convert the The 8 characters starting from the 7th digit are converted into the format of "00年00月00日".

The formula for extracting gender information means that if the ID number is 15 digits, divide the 15th digit by 2. If it is divisible, it will be displayed as female. If it is not divisible, it will be displayed as male. If the ID number is 18 digits, divide the 17th digit by 2. If it is divisible, it will be displayed as female. If it is not divisible, it will be displayed as male.

In addition, if you want to convert the date of birth into a real date type, just add two minus signs before TEXT in the formula, which is a minus operation, and then set the specific date type of the cell as needed.
The above is what the editor said aboutHow to extract the content of ID card information from WPS form, I hope it will be helpful to everyone. For more exciting tutorials, please pay attention to Huajun Software Park