Tips for correctly displaying your identity card numbers in Excel cells

Source: Internet
Author: User

For more than 12 digits (including 12-bit), Excel automatically displays the scientific notation and looks uncomfortable. This is the case when we want to copy the word form with the ID number to Excel. So, how should the identity card number should be kept in the original?

First, the correct replication steps

1. Start Excel, depending on the number of rows in the Word table, select the area you want to copy to (or select the entire worksheet if the data format is not required).

2. Right-click in the selection area and select Format Cells command.

3. In the Format Cells dialog box, select text on the Number tab, and then click OK.

4. Select the table in the Word document to perform the copy operation.

5. Switch to Excel, keep the setting area selected (or click to select the first cell in the upper-left corner of the range), and then choose one of the following two methods.

(1) perform "edit" → "paste selectively", select "Text" in the dialog box, and then click "OK" button.

(2) You can also paste, and then click Paste Options in the lower-right corner of the pasted object to select the match destination format item.

This allows the identification number to appear in the form of text.

Ii. recovery of 15-digit identification numbers

In Excel, when you enter a 12-15-digit number, it appears as a scientific notation, but its numeric size is unchanged. Today's ID card number has 15 digits and 18 points. If the 15 ID card number appears in the form of scientific notation, it can be set up to show the "truth" of the ID card number.

You can set it in the Number tab of the Format Cells dialog box.

Method One: Select value in category, change the number of decimal places from the default "2" to "0", and click OK. If this is the "0" bit, click the OK button directly.

Method Two: Select the "Score" in "category", and then select any of the fraction types, click the "OK" button.

Method Three: Select text in category, click OK, and then double-click the cell to change.

Method Four: Select "Special" in category, and then select "Postal Code" in Type, and click "OK" button.

Method Five: Select Custom in category, then set to 000000000000000 (or replace "0" with "?", "#"), and click the OK button.

In Excel, without setting the direct input or paste more than 15 of the number, the system will automatically replace the value of "0", for example, the direct entry of 18-digit ID number, the last three digits will be "000" replacement, can not be as accurate as 15 ID card number to display. Therefore, it is necessary to first set the paste area to text cell format before copying.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.