OmniDocHow to identify gender from ID number in Excel
Use Excel formulas to determine men and women from 18-digit or 15-digit ID numbers, and avoid long numbers being displayed in scientific notation or ending in 0. Explain how to write formulas, check digits, and check data.
Key points of this article
- The 17th digit of the 18-digit ID number is the gender code. Odd numbers are male and even numbers are female. Use MID to take it out and then use MOD to judge.
- The gender code of the 15-digit old ID card is at the 15th digit. The judgment rules are the same. The formula must first be branched by digits.
- The ID number must be saved as text, otherwise Excel will convert the long number into scientific notation, and the last few digits will become 0.
- Formula results need to be randomly checked for digits, duplicate values and abnormal values. Records with incorrect digits are marked separately for manual verification.
There are hundreds of ID numbers in the list, which need to be classified by men and women. Manually reading them one by one is slow and error-prone. Many people will think of letting the form "identify" gender directly, but for the ID number that has been entered, the reliable way is to use formula calculations instead of identifying pictures. This article is written for people who need to organize employee, student or member lists. It explains how to use Excel formulas to derive gender from ID numbers, and how to ensure that long numbers are displayed correctly.
This judgment is based on the encoding rules from the ID number itself. The gender information of the citizen identity number is hidden in a fixed position. As long as the position and format are correct, the formula can give stable results. After reading this article, you will be able to apply formulas directly to your own spreadsheets and know where to start when the results are wrong.
How does the form identify male and female ID cards? One sentence conclusion
Direct conclusion: Select the number position according to the number of digits in the ID number. For an 18-digit number, take the 17th digit. For a 15-digit number, take the 15th digit. If the 17th or 15th digit is an odd number, it is recorded as a male, and an even number is recorded as a female.
| your situation | Recommended practices | reason |
|---|---|---|
| 18-digit ID number (current standard) | Use MID to get the 17th bit, and then use MOD to take the remainder of 2 | The 17th digit is the gender code, odd numbers are male, even numbers are female |
| 15-digit ID number (early years ID) | Use MID to get the 15th bit, and the judgment rules are the same as above. | The gender code for a 15-digit number is at position 15 |
| Two digits mixed in one column | Use IF to press the LEN branch and take the corresponding position. | One formula handles two situations at the same time to avoid missing calculations |
| Numbers from PDF or image | First convert the number into table text, and then write the formula | Formulas can only process text already in the cell |
| Number of digits is not 15 or 18 | Displays "wrong number of digits" and does not determine gender | If the number is wrong, forced judgment will result in meaningless results. |
Before you start: Confirm these points first
1. Whether the ID number has been entered completely. If the number comes from a scan, first confirm that each digit is not missed. The formula will only take the number according to the position, if one bit is missing, all subsequent positions will be misaligned.
2. Whether the number contains spaces or invisible characters. Numbers copied from web pages or other systems often have leading and trailing spaces. Spaces will make the number of digits calculated by LEN inaccurate, leading to incorrect judgment. You can clean it up first by trimming spaces.
3. What you want is gender judgment, not identity verification. The gender code only reflects the coding rules and cannot prove who the person corresponding to the number is, nor can it determine whether the number is real and valid. When it comes to formal purposes, the original certificate should prevail.
4. Is there any personal privacy data in the list? ID number is sensitive information. When processing, try to complete it in a local form. Do not upload the entire list of numbers to an unknown online tool just to save trouble. After the statistics are completed, only men and women can be retained in the result column, and the original numbers are properly kept or desensitized according to the unit system.
5. Is the file a PDF or a form? If the number only exists in PDF, you can first use the PDF editor of OmniDoc desktop client to perform OCR recognition, then export it to Excel, and then write the formula after checking it.
Steps to use formula to determine men and women
Assume that the ID number is in column A, starting from A2, and the result is written in column B.
- Open the list in Excel and confirm that the ID number is in column A and the first data is in A2.
- Select the area in column A where you want to enter the number, first format the cell as text, and then paste or enter the number. This step must be completed before entering the number.
- Enter the judgment formula in B2:
=IF(LEN(A2)=18,IF(MOD(VALUE(MID(A2,17,1)),2)=1,"男","女"),IF(LEN(A2)=15,IF(MOD(VALUE(MID(A2,15,1)),2)=1,"男","女"),"位数错误")). - Press Enter, confirm that B2 displays "Male" or "Female", and check whether this item is consistent with the ID.
- Double-click the fill handle in the lower right corner of B2, or drag to the last piece of data to fill the entire column with the formula.
- If you need to count the number of people, you can use COUNTIF in a blank cell to count the number of "males" and "females" respectively, such as
=COUNTIF(B:B,"男"). - Manually check the rows with "wrong digits" one by one. After correcting the numbers, the formula will automatically update the results.
VALUE in the formula converts the extracted text into numbers, MOD takes the remainder of 2, and the remainder is 1, which is an odd number. If your table version is strict about automatic conversion of text and numbers, this level of conversion can avoid invalid judgment.
Check each item after completion
After the results come out, don't take the statistics directly, do the following tests first.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Whether the number display is complete | See if there are scientific counting methods such as E + in column A, and whether the last few digits are all 0. | The number has been truncated and cannot be recovered. You need to re-import it from the original list and make sure it is formatted as text first. |
| Is the number of digits correct | Add another column to count digits with LEN, and only 15 or 18 should appear. | Find the line of "digit error", check the original and correct it. |
| Is the gender reasonable? | Spot check more than ten items and compare them with the original or list remarks | If the whole paragraph is wrong, check if there is a space, or if the position formula is written in another position. |
| Is there a duplicate number? | Use COUNTIF to count the number of occurrences of each number in column A | Duplicate records should be confirmed twice to avoid double counting of the number of men and women. |
| Last bit check code | Optional: check bit 18 with verification rules | Most of the numbers that fail the verification are entry errors and should be returned for verification. |
If you need to check more strictly whether the input of the 18-digit number is correct, you can use the check code rule: the first 17 digits are multiplied by a fixed weight, the remainder is taken from 11, and then the 18th digit is obtained against the remainder table. pay attention to capitalization when the last bit is X. This step can only find the typing error and cannot prove the real existence of the person corresponding to the number.
# Core comparison
Several common judgment methods are applicable to different scenarios:
| Judgment mode | Applicable scenario | Matters needing attention |
|---|---|---|
| Use the formula to calculate from the number | The number is already in the form, the quantity is large and the request can be reproduced. | The result is stable. Change a number and update immediately. |
| Check item by item manually | Occasions where the number is very small and manual confirmation of documents is required. | Slow, but you can find problems other than the number. |
| Use pictures to identify gender | Is not suitable as a basis for judgment. | The ID card number will not be directly displayed in the picture in the form of gender, and picture recognition cannot replace the coding rules. |
# frequently asked questions (FAQ)
1. Why does the table identify gender formula turn out to be all "female" or all "male"?
In most cases, the number is saved as scientific counting or the text format is inconsistent, resulting in the 17th digit taken out is not a real number. First check whether the number in column An is complete, and then see if the position in the formula is correct.
2. What should I do if I enter my ID card number and change it into Epig17?
By default, Excel treats more than 15 digits as numeric values, and the last bit will be forced to 0. This kind of data cannot be recovered by changing the format. The right thing to do is to set the column as text and then enter it, and the wrong number should be re-imported from the original list.
3. How does the form identify the ID card number correctly? Can the formula tell whether it is true or not?
The formula can only check the number of digits, format, and check code rules, and cannot prove that the number belongs to a real person. It is suitable for finding entry errors, and formal verification still depends on documents and authoritative channels.
4. 15-digit and 18-digit numbers are mixed in one column, should I deal with them separately?
You don't have to split up. The above formula is automatically branched with IF and LEN, each of which takes the corresponding position, and the result is written in the same column.
5. How can the ID number in the scanned piece be quickly changed into tabular data?
You can first use OCR to identify the scanned pieces, organize the text into a table, and check the recognition results one by one, especially the easily confused numbers such as 0 and 8, 1 and 7.
# Summary
Using Excel to judge male and female ID cards, the key is to take the right position according to the number of digits: 18 bits to see the 17th bit, 15 bits to see the 15th bit, odd numbers for men and even numbers for women. Set the number to text before doing it, and then check the number of digits, repetitions and outliers, and the table can stably give statistics on men and women.
