Using Quick Fill in Excel
Since Excel 2013, Microsoft has added a new useful feature, QuickFill. As soon as Excel recognizes a pattern, the data is automatically completed. Sometimes Excel does this automatically, but you can also call the function with the keyboard shortcut CTRL+E.
You can download the sample file at the bottom of this page to download so that you can follow everything step by step.
Suppose we have a list of personal names where the first and last name are in the same cell (column A). We want to split this into two columns. In the first column we want the first name and in the second column the last name.
Create two new columns. Name the first column (column B). First Name, you name the second column (column C). Last name.
Fill in cell B2 the first name from cell A2 in. Fill in cell C2 the last name from cell A2 in.
Select cell B3 and press the key combination Ctrl+E in. Excel automatically fills in all first names in column A. Repeat this step for the surnames in cell C3 the key combination Ctrl+E applying.
In addition to splitting data, it is also possible to merge data. If we also have telephone numbers in addition to the personal names, we can paste the last four digits of the telephone number after the first name to create a (for example) login name.
The first column (column A) is called First and last name. The second column (column B) is called First Name, the third column (column C) is called Last name.
Name the fourth column (column D). phone number.
Fill in cell E2 the first name from cell A2 in followed by the last four digits of the phone number in cell D2.
Select cell E3 and press the key combination Ctrl+E in. Excel automatically fills in all first names listed in column A, followed by the last four digits of the telephone number from column D.
If we have a list of telephone numbers without any formatting, this is difficult to read. Placing spaces or a character between a series of numbers improves readability and reduces the chance of reading errors.
Suppose I have a list of telephone numbers in column D without formatting. Now I want -character or a space between the third and fourth and seventh and eighth digits to increase readability.
Fill in cell E2 enter the phone number and place spaces or characters at the desired positions.
Select cell E3 and press the key combination Ctrl+E in. Excel automatically takes over the formatting of the phone number you enter in cell E2 entered.
If you can't figure it out with the above guide alone, download the sample file or ask a question by leaving a message.