How to use Excel 2016 "quick fill"

1. Extract numbers and strings

Many times, we need to extract the numbers or strings in the string, but the source data often lacks the rule, for example "specification: 10th, length 24232 meters, quantity 1" In the data, we need to extract the specification, the length, the quantity and so on the change number, obviously here cannot directly use the left, the right, MID, find and other text function extraction, need to use some more complex formula, which is more difficult for the novice user. Using the Quick Fill feature is a lot simpler.

If you manually enter "10" in the normal way, and then select the target cell that you want to fill, then select fill → quick fill in the Edit Feature group, and you will not get the correct extraction results in subsequent cells, only the corresponding results will be displayed according to the starting character. The correct way is to manually extract 2 digits in the first two cells, then manually enter the starting number in the third cell (Figure 1), and the fill suggestion is automatically displayed, so we just press the ENTER key. The method of extracting length and quantity is exactly the same.

2. Extraction Merge One step realization

The quick Fill feature not only enables batch extraction, but also combines different contents of two column cells while extracting. For example, B3 cell is "0001-Liu Yi", C3 Cell is "deputy General manager", now want to get the result of "Deputy general manager Liu", can also use "quick fill" to solve this problem.

In the D3 cell manually input "Deputy general manager Liu," then in the D4 cell manually input "Chen", you can see the effect shown in the figure (Figure 2), directly press the ENTER key to complete the extraction and merging operations.

Small tip:

This method also allows you to complete code and name, name, and position merging.

3. Adjust the Order of strings

Sometimes, we need to adjust the sequence of strings, such as "Money money Cash and Cash equivalents" to "Cash and Cash equivalents Money", which often requires complex formulas to be implemented. The Quick Fill feature enables you to quickly adjust the order of strings.

Manually enter "Cash and Cash equivalents Money" in the target cell, and then continue typing in the next cell, and you will soon see the fill recommendation effect shown in the figure (Figure 3), and the fill can be completed directly by pressing the ENTER key.

4. Capitalization conversion and reorganization

If you need to convert the first letter of the phonetic name to uppercase, for example, "Lohuimin" and "Luo Huimin" converted to "Luo luo huimin huimin" effect, here not only involves the problem of case conversion, but also involves the surname, name split and phonetic combination, manual conversion is obviously quite troublesome, And it's easy to make mistakes. Use quick fill to complete capitalization conversions and reorganizations.

Manually enter the first-letter capitalization data in the target cell, and then continue typing in the next cell, and then press the ENTER key directly after you see the fill proposal shown in the figure (Figure 4).

5. Date of birth from ID card

We know that the ID information contains the date of birth, and in previous versions of Excel, a function or formula was needed to extract it. With Excel 2016, you can extract this information directly using the quick fill. However, before you populate, set the format of the destination column first.

Open the Format Cells dialog box, switch to the Number tab, select the Custom category, select Yyyy/mm/dd on the right side, and then close the dialog box after confirming.

Manually enter the date of birth in your ID number in the destination cell, select the range of cells you want to quickly fill, and press the "Ctrl+e" key to see the effect shown in the figure (Figure 5).

6. Automatically add string delimiters

There are times when we need to separate some strings with spacer numbers. For example, you need to modify the "603665" to "60-36-65" effect, here not only involves the extraction of data, but also need to add the separator "-", in the past we are through the formula and "&" to achieve data connectivity. Now, we can also use the quick fill to easily implement.

Manually enter "60-36-65", and then connect three times to enter similar data, you will soon see the figure as shown in the fill proposal (Figure 6), directly press the ENTER key on it. You need to be reminded that you need to enter at least three examples to get the correct results for a quick fill.