For this lesson you need to open the sample file: Client Names-1 from your Sample Files folder.
Up until now we've been using Flash Fill to extract delimited data into discreet data elements. For example, we used Flash Fill to split Jessica Sagan in one cell into two separate cells. One containing the name Jessica and one containing the name Sagan.
In this lesson we're going to use Flash Fill to not split text, but concatenate text. Let me show you what I mean by concatenation. Cells A4, B4 and C4 contain three pieces of text: Sagan, Elizabeth, Jessica. In cell D4 I'm now going to concatenate them by typing: Sagan Elizabeth Jessica. You can see that all concatenation means is to take text values from several separate cells, add a delimiter next to each value, and place the result in a single cell.
In this case I've taken the three discreet pieces of data: Sagan, Elizabeth and Jessica, and then I've put a space in between each piece of data to produce Sagan Elizabeth Jessica. This type of concatenation is very simple and Flash Fill finds it very easy to perform. But we're going to push Flash Fill to the very limits of it's power in this lesson by giving it a problem to solve that seems, at first, to be almost insolvable.
Let's delete Sagan Elizabeth Jessica from this cell and, in cell D3, we'll type the header: Formatted Name. And let's consider how we'd like the name Sagan, Elizabeth, Jessica to be formatted in quite a complex way.
Let's have Sagan (the surname) first, then a comma, then a space, then the First Name and then a space, and then a capital letter showing the initial of the Middle Name, and finally a full stop.
Now that seems like quite complicated problem but you can see that, for Flash Fill, it's going to be impossible for it to work with the remainder of the data. For example, on Row 5, Steven Bell doesn't have a Middle Name: he has an initial, and the initial's lower case. So how can Flash Fill possibly guess what we want to do with that?
And look at John Jennings, he doesn't have a Middle Name at all and his last name has a lower case j. How can Flash Fill figure out what result we'd like for John Jennings?
Well just for fun let's give Flash Fill the impossible task and see what it makes of all this data. So I'll click Home tab on the Ribbon, Editing group, Fill, and Flash Fill.
Well Flash Fill's had a shot at the problem, but it's not done very well. In the case of Steven Bell, it's got the First Name and Last Name okay, but it's really messed up with the Middle Name. It just didn't know what to do with that lower case p.
And with John Jennings, I can't imagine where it's got an O from.
And the first letter of jennings is lower case, because Flash Fill's guessed that I want to keep the original formatting.
It's the same with the last three names. Really, Flash Fill hasn't done well. But it's not Flash Fill's fault, it's our fault for not telling Flash Fill what's needed.
So let's give Flash Fill another hint. I'll take away those bad names and, in the case of Steven P. Bell, I'll ask Flash Fill to produce the name Bell, Steven, and then a capital P, and a full stop. That should help Flash Fill do a better job.
Let's try: Home tab on the Ribbon, in the Editing group, Fill, and Flash Fill.
Look what Flash Fill's done this time. It's said: "On the information that you've given me I don't have a problem with Row 7", (and it's got that completely correct). "And I can also figure out what to do with row 9, because that's very similar to Steven Bell". But Flash Fill is saying: "I haven't got a clue what you want me to do with John Jennings and Alfred Hawking". So it's left the cells blank.
Let's tell Flash Fill how we'd like John Jennings to be formatted. Well I'll go for Jennings with a capital J, then a comma, and then John with a capital J and I'll press the Enter key.
And look what's happened. Flash Fill has said: "Aha! Now I know how to format John Jennings, I'm quite happy that I also know how to format Alfred Hawking." And what seems to be an almost insolvable task has been completely solved by Flash Fill.
You can see that, in this lesson, Flash Fill was able to work its magic because we gave it three separate examples of how we wanted it to treat different formatted names in the source cells. This is called Multiple Example Flash Fill.
You can now begin to appreciate how immensely powerful Flash Fill is, but we're not finished yet. In the next lesson, I'm going to demonstrate some even more incredible things you can do with Flash Fill.
But for the moment all that remains is to save your work. And I'm going to save with the new name: Client Names-2. And as usual I'll save to the folder above my Sample Files folder.
I click the Save button and you've now completed Lesson 2-20: Use Multiple Example Flash Fill to Concatenate Text.