For this lesson you need to open the sample file: Employee Names-1 from your Sample Files folder.
This worksheet contains Names in Column A, but you wouldn't normally put names into a column like this. It's always better to split names into: First Name and Last Name.
The type of data in Column A is called Delimited data. This means that there is more than one value in each cell, and the cell is delimited by some sort of character. In the case of the First and Last names, each name is delimited by a space, but you can imagine that you would sometimes see this sort of data delimited by a comma. And you might even see the data delimited by a dash.
Whatever the delimiter is, it's still delimited data. And a very common requirement in Excel is to split delimited data into separate cells for each data element. In this case we want to split Name in column A into First Name, and Last Name in columns B and C.
I've typed First Name and Last Name into the header row for Columns B and C. And now I'm going to boldface the headers using the Mini Toolbar that you learned about in Lesson 1-15. To do this I select cells B3 and C3, right-click with the mouse, and on the Mini Toolbar I'll click the Bold button.
There are many ways in which I could split the Full Name, in column A, into the First Name and Last Name, but Excel 2013 has introduced a major new feature called Flash Fill.
Flash Fill makes it really simply to split delimited data into its constituent parts. When you split data using Flash Fill it's important that the headers, (that's Name, First Name and Last Name), are formatted in a different way to the data, (that's all the names in column A). This is so that Flash Fill is aware that Row 3 is a header row, and Flash Fill won't attempt to split any data in that row.
The header row doesn't have to be boldfaced, it could be a different colour or a different font, provided it's formatted in a different way to the data beneath.
So let's now use Flash Fill to split the First Names out of column A and into column B. To do this I click in cell B4 and I type: Jessica.
Now it's very important that you capitalize and spell Jessica in exactly the same way it's capitalized and spelled in cell A4.
I'll now press the Enter key and I'll type a capital S, that's the first letter in Steven Bell's name. And look what's happened. Flash Fill has figured out that you might want to split the First Names in column A. So it's shown its guess and then it's shown what the split data would look like grayed out in all the other cells. All I now have to do is press the Enter key to use Flash Fill to separate the first names.
I'll press the Enter key now and you can see that I've separated all of the First Names. And if you look at the bottom left of the screen you can see that Flash Fill has actually extracted 23 First Names from Column A.
Let's now use Flash Fill to extract the Last Names. So I'll click in cell C4, type: Sagan. Once again it's really important that you spell Sagan and capitalize it in exactly the same way as in column A. Then I'll type the capital B of Bell and once again Flash Fill has worked perfectly. I'll press the Enter key on the keyboard and all of the last names are extracted.
Let's now look at another amazing ability of a Flash Fill: the ability to extract initials from names.
In cell D3, I'll type the word: Initials, and press the Enter key. Now I'll type JS, (that's Jessica Sagan's initials), press the Enter key and type S. That's Steven Bell's first initial.
And you can see that Flash Fill has anticipated my needs again. I'll press the Enter key and I now have initials for all of the Names in columns A, B and C.
At this point you might be very impressed with what Flash Fill has done, but the text in Column A was very simple delimited text. In the three lessons that follow, we'll explore some more complex Flash Fill techniques that will enable you to perform some very advanced text splitting tasks.
But for the moment, all we need to do is save the file, and we'll save it with the new name: Employee Names-2. And as usual I'll save the folder above my Sample Files folder.
I click the Save button and you've now completed Lesson 2-18: Use Automatic Flash Fill to Split Delimited Text.