Excel quickly restores the wrong entry ID number two strokes

At the beginning of each year, the school should submit materials to the personnel Department for the in-service staff. In Excel will be the staff situation input, can be ID card number This column input the teacher did not set the first column of the cell format as text, resulting in ID number in the input after the system automatically converted the format, and the actual submission requirements do not match (correct, A quick way to enter an ID number many of the information has been introduced, here no longer repeat.

Input of the teacher to me for help, from the figure one can see that the selected ID number in the formula bar is fully displayed (Figure 1), if one by one to copy and paste out, time-consuming and laborious. Later, I try to use two simple methods to successfully recover the wrong entry of the ID card number.

Figure 1

1. Use the text function

Insert a blank column after the "ID number" column, click F2 Cell and enter the formula =text (E2, 000000000000000), which replaces the contents of the E2 cell with the specified format (15-bit "0") in the formula, and the correct ID number is received after pressing ENTER. Use the fill handle tool to copy the formula to another cell (Figure 2).

Figure 2

After the operation is completed, you cannot directly delete the original ID number in the column E, otherwise the F column data will not display correctly, to print the table, you can hide the e column or select the F column data, click the Copy button, and then right-click to select the paste Selective command, and select the value from the pop-up Paste Selective dialog box. Then you can safely delete the e-column data of the original ID number.