How to extract the month from the excel ID card number: first make a wps table template; then intercept the birth year and month data in the ID card number through the MID function, and use the TEXT function to convert it into the "year-month-day" format ;Finally, extract the month through the month function.
The operating environment of this article: windows7 system, WPS Office2016 version, Dell G3 computer.
Recommended: "Excel Tutorial"
First we make a wps table template. The company's common employee information table generally includes name, ID number, date of employment, etc. field. A citizen's ID card number contains information such as the citizen's region, date of birth, gender, etc. What we need to do is to extract the date of birth through the excel function. As shown in the picture:
We click on the birthday in the common birthday field. The function we need to use: MID, which is a character interception function. The ID number starts from the 7th digit. The beginning is the birthday information. The birth year, month and day are 8 digits, so it is MID (birth year, month, 7, 8). The intercepted format is 19891207. In this format, we then use the TEXT function to convert it into the year-month-day format. , so the formula is: = TEXT(MID(B2,7,8),"0000-00-00")
As shown in the picture:
Like this We get the employee's birth year, month and day, and then extract the month on this basis. The birth year, month and day information is as shown in the figure:
To extract the month information, we need to use The commonly used date function month() in excel is to extract the month, and month also means the English month. So the formula is:
In this way we get the employee’s birth month information, as shown in the figure:
Next, we use the commonly used filtering function in wps tables to filter months. The operation is shown in the following three pictures:
The above is the detailed content of Excel ID number extraction month. For more information, please follow other related articles on the PHP Chinese website!