OmniDocHow to set the input format of Excel ID card number
When entering an ID number in Excel, the numbers are often displayed in scientific notation, and the last few digits will become 0. This article explains how to set the text format first and then enter it, as well as how to handle existing data, pasted data, and PDF data, and provides a checklist.
Key points of this article
- The ID number must be formatted as text before entering, otherwise Excel will process it as a number.
- Excel only retains 15 significant digits, and any excess digits will be truncated to 0. This type of data cannot be recovered by changing the format.
- Set the format before inputting, and then use formulas to extract birthday and gender without making mistakes.
- Numbers copied from web pages and PDFs must be converted into plain text before pasting, and then the number of digits and the last digit must be checked.
Entering the ID number in the form is a pitfall for many people for the first time. After entering an 18-digit number, the cell may display scientific notation like 3.21E+17, or the last few digits may suddenly become 0. By the time the problem is discovered, the data has often been entered incorrectly, making it troublesome to remedy.
This article is written for readers who need to organize personnel lists, registration information, and file ledgers in Excel. You will learn why these problems occur, how to set the format correctly before recording, how to deal with existing data and data copied from outside, and how to check item by item after recording. After reading this, you can safely use formulas to extract the date of birth and gender from the ID number without worrying that the previous data has been distorted.
How to fill in the ID number format in the form? One sentence conclusion
Conclusion: Before entering, set the cell or entire column where the number is to be filled in to text format, and then enter it again. Data that has been displayed in scientific notation will not be automatically restored after the format is changed and needs to be re-entered.
| your situation | Recommended practices | reason |
|---|---|---|
| Create a new form and manually enter a small number of numbers | First select the column, set it to text format, and then enter it one by one | When entering, it will not be treated as numbers, and it will be done in one go. |
| Data that has been entered and displayed in scientific notation | Set it to text format and re-enter it. Do not just change the format. | When displayed in scientific notation, the last few digits have been lost and cannot be restored by changing the format. |
| Copy a number from a web page, document or chat history | Paste as plain text and check the number of digits after pasting | Direct pasting may bring in hidden characters or still process it numerically |
| Number from PDF or scan | Recognize text first, then copy to table, or export to Excel and check | Text cannot be directly selected in the scanned document, and the recognition results need to be checked manually. |
Before you start: Confirm these points first
Before starting, you can avoid half of the rework by judging the following items.
- Confirm whether the number is 18 or 15 digits. The current resident ID card has 18 digits, and the last digit may be a number or the letter X. The old version of the certificate issued before 1999 has 15 digits. The rules for entering this type of data are different and the source needs to be checked separately.
- Confirm where the data comes from. Manual entry, copy and paste, import of external files, and fetching numbers from PDF, the four sources are processed differently. The source is unclear, and it will be difficult to determine what went wrong later.
- Confirm whether there is data in the table. If a large number of numbers have been entered in the original table, first check which cells have been distorted. Format settings are only effective for subsequent input.
- Confirm whether subsequent calculations are required. If you later use a formula to extract the birthday or gender from the number, the number must be saved as text. Text format is the prerequisite for formulas to obtain correct values.
- Confirm that no numerical operations are required. The ID number is an identifier, not a quantity, and should not be summed or involved in comparing sizes. Treating it as text is in line with its nature.
Steps to set the text format of ID number in Excel
The following is the standard operation before entering the ID number. Set the entire column once, and the numbers entered thereafter will be saved as text.
- Select the column or area where you want to fill in the number. Click on the column label to select the entire column. If there are only a few rows, just drag and select the corresponding cells.
- Open cell format settings. Right-click on the selected area and select Format Cells, or use the corresponding shortcut keys.
- Change the category to text. Select text in the category list and close the window after confirmation. At this time, the cell has no content, and the changes only affect subsequent input.
- Enter the first number and check. Enter a number and check whether the cell displays all 18 digits and whether the content in the edit column is consistent with the certificate.
- Enter the remaining numbers in batches. After the format is set, just enter it line by line in the normal way. There is no need to add a prefix to each cell.
- When temporary input is required, you can add a single apostrophe before the number. Enter an English single apostrophe and then the number, Excel will save it as text, and the single apostrophe itself will not be displayed in the cell. This is only for individual cells and is suitable for small amounts of supplementary recording.
- When importing from outside, set this column to text in the import step. When importing text or table files, most import wizards can specify the data type for each column. The column selection text where the ID number is located can avoid being automatically converted into numbers after import.
- Save the file after recording. After saving, open it again and confirm that the number display has not changed before giving it to others to use.
If the number originally came from a web page or document, you can paste it into the plain text position when copying, remove the formatting, and then copy it into the table. After pasting, check the number of bits as described in the previous section. If any abnormalities are found, redo immediately; do not wait until the entire table is recorded before processing it all at once.
After completion, check each item one by one
After entering the data, don't just glance at it and stop there. The following check-up items one by one can reveal most common problems.
| Inspection items | How to see it | What should you do if there's a problem? |
|---|---|---|
| Is it fully displayed in 18-bit mode? | Check the cell display and compare it to the editing bar | Incomplete display indicates insufficient column width or still numeric format; adjust and re-enter the data |
Whether the words E+ appear | The cell will show E plus a sign and a number | Note: After processing as a number, set it to text and re-enter it; you can't just change the format |
| Whether the end becomes a continuous zero | Looking from back to front, the last three to four are the last ones | Parts exceeding 15 significant digits have been lost and can only be recovered from documents or the original list |
| Whether the last letter X is kept | Check if there are numbers ending in X | If X is lost or becomes another character, re-enter and confirm uppercase |
| Is the digit number 18? | Use formulas to count length, or randomly check each item | Incorrect digit numbers indicate missing inputs, over-inputs, or missing spaces; correct each entry one by one |
| Check for extra spaces or apostroscopes at the beginning | Click into the cell to see the beginning of the editing bar | Delete any extra spaces; If the apostrophe is displayed in the editing bar, it is a normal text markup |
| Check if the alignment is abnormal | Text is left-aligned by default, numbers are right-aligned by default | If it is still right-aligned, it means the cells may still be numeric and need to be reset |
When verifying, it is recommended to randomly check a dozen or so lines and compare them with the original ID or list, rather than just looking at the first few lines. If you find issues in a batch, it is usually due to errors in import or pasting, and you should return to the source to rework.
❓ Frequently Asked Questions (FAQ)
1. If the table already displays the Scientific Notalion ID number, can I restore it by changing it to text format?
No. Parts of Excel that exceed 15 significant characters are rounded to zero, and changing the format only affects subsequent inputs. These numbers should be re-entered from documents, original lists, or other reliable sources.
2. Why did I set it to text format, but the numbers still displayed as scientific notation?
In most cases, data was entered before the settings, or the copied-paste data still contains numeric format. After setting a cell as text, you need to re-enter or paste it again to save it as text.
3. After setting the format, how do you use a formula to retrieve your birth date from your ID number?
When the number is in text format, you can use the MID function to extract digits 7 to 14, which is the birth date. The result after extraction is still text; it needs to be converted to date and processed separately. Several formula results should be randomly checked to confirm consistency with the certificate.
4. How do you enter the ID number in a scanned PDF into a table?
The scanned image cannot directly select text; you need to recognize the text first. The PDF editing features of the OmniDoc desktop client include OCR recognition, which can be exported to Excel after recognition. The download link is download.omnidoc.top. The recognition result is machine-based and must be checked against the original after entry, especially the last letter and similar numbers.
📝 Summary
How to fill out the ID number format in a table? The core is simple: before entering, set the cells as text, then check each item and the number of digits one by one. Data that has already been distorted cannot be recovered by changing formats; it must be retrieved from the original source. Set the format correctly, then use the formula to extract the birthday and gender; only then can the results be reliable.
