In many cases, the data we enter in Excel cells contains fixed content, for example, the first six digits of the ID card number from the same district or county must be the same, or the contact address of the person from the same county or province or city. The county part is also fixed. In this case, if the input is honestly and sequentially, the efficiency is very low. How can we quickly and accurately input these data with fixed content? I think one of the following methods can help you solve the problem.
If the data contains fixed content, but at the same time is regular data, such as the employee number of the employees of the unit, in a certain sorting method, they are a regular sequence. The easiest and most straightforward way to do this in this case is to use autocomplete.
For example, the data to be input is “12345××××”, the last four digits are different, and the first five digits are the same. Then we only need to enter “123450001”, “123450002”, “123450003” in the first, second and third cells respectively, then select these three cells, and drag the fill handle of the cell range down to The required position can be automatically filled.
If the fixed content is text, but as long as the number part is a regular sequence, such as “one workshop 001”, “one workshop 002”, etc., this method can also be used.
If the data that needs to be entered cannot be filled automatically, then we can also simplify our work by setting the cell format. For example, the data to be input is a set of 14-digit numbers, of which the first ten digits are all “0737070680”, and only the last 4 digits are different. By setting the cell format, you can only enter the last 4 digits and let the first ten digits be added automatically.
The implementation method is: select the cell area where the data is located, then right-click, and select the “Format Cells” command in the pop-up shortcut menu to open the “Format Cells” dialog box. Click “Custom” in the “Category” list of the “Number” tab, and then enter “0737070680000#” (without quotation marks) in the “Type” input box on the right Enter “0” in the first three digits of the four digits, and enter “#” in the fourth digit. In this way, as long as you directly enter “1”, “2”, “3”, etc. in the cell, you can directly complete the 14-digit number.
If the data contains fixed text, for example, the first few words are “Weifang City, Shandong Province”, then we can also enter “”Weifang City, Shandong Province”@” in this custom “Type” input box. These words can also be automatically added when typing in the future. This method displays all the data on the screen, but clicking on the cell we can see in the formula bar that the actual data is just the part we entered manually.
Suppose the data contains the fixed content “Weifang City, Shandong Province”, which needs to be entered into the cell range A2:A10. We can enter the contents of the data except the fixed contents in the B2:B10 area first, then click the A2 cell, enter the formula “=” Weifang, Shandong Province” &B2″, and drag the fill handle of this cell to copy down after completion. The complete data can be obtained by applying the formula to cell A10.
Then select the cell range A2:A10 and press “Ctrl+C” to copy the data. Right-click, click the “Paste Special” command in the pop-up menu, select the “Value” radio option in the opened dialog box, and after confirming, you can delete the data in column B.