Use Flash Fill to rapidly combine or separate columnar data

- [Voiceover] One of Excel's most powerful tools, introduced in Excel 2013, is called Flash Fill. I've got different sets of data on this worksheet here. Column A is a list of names, and nothing truly wrong with those but if these are next to other columns where I've got contact information, I can't sort my list by last name. Now, there is a technique for splitting the data into separate columns. We can do this by way of the data tab and a feature called text to columns but many times because of the new feature, we won't need to use text to columns.

It still has other uses. What I'm going to type in cell B2 here is Frost, comma, space, Adam and there are different ways to approach this. One approach is to type an entry, press enter, start the second entry, now if the second entry's going to be in the same style, I'll be typing Hood but as soon as I type capital H, Excel effects says, "I see what you're doing," and it automatically copies the entries down the column. I'll simply press enter. So, we've got a whole list of corrections here.

This goes down to row 51, of course the list could have been much larger. That's the way the data looks, I'll keep this and maybe move the title over and throw away the data in column A. Don't need it anymore. Now I've got a list where I can sort the data by last name. Another scenario, same idea with names but this time we want to pull the names together. In this case we only have first and last names. Now, in addition to pulling them together, we can also change case and I could have done that with the previous example. In other words over here, I could have typed these just capitalizing the F in Frost and the first A in Adam, Excel would have picked up on that and this so-called flash feature would have worked there as well, too.

Let's go into column E and we'll show you a different way to use Flash Fill in two different ways. First of all, we'll join the names this time and I want the names to go in the order last name, comma, space, first name and I don't want the names to be capitalized, just the first letters. Baker, comma, space, Mark and this time I'll complete the entry by typing Control + Enter, so the active cell doesn't move and point out that we could go to the data tab and choose Flash Fill and there's the description.

Automatically fill in values. Enter a couple of examples you want as output and keep the active cell in the column you want filled in. Now, I've only entered one value, but nevertheless, if I click Flash Fill, Excel figures that out. It even looks upward and takes that data and puts it in order that way which I might keep or might not but this is all cleaned up. Now on all examples here, we don't have any other extraneous issues like trailing spaces, leading spaces, that could alter the perception of how this works and sometimes it doesn't work correctly if you've got those issues.

Now, another approach here, and I'll press Control + Z, there is a keystroke shortcut for it so after typing the name, I'll press Control + Enter, then I'll press Control + E, that's the shortcut and the entries are made that way, so it gets even faster. So what we did this time is not only did we pull the information together from different locations, I even changed the order and Excel picked up on that and off to the right we've got a slightly different situation and this is to point out that although Flash Fill is a great tool, it can't get everything quite right.

I'm going to pull these names together, too, and notice that some of the people have middle initials and the first person does have a middle initial. Let's see what happens here. Last name, Sams, comma, space, Judy, space, capital D. I'll press Control + Enter so the active cell doesn't move, Control + E is the shortcut, there it is. It looks like that's working fine. Wherever there is a middle initial, like right here we see it, right here we see it, in other cases we don't, there is a trailing space there that possibly could be an issue.

We'll show you how to deal with that momentarily. Now, I'm gonna undo that and you could imagine what might happen if the first person didn't have a middle initial. Maybe the second one does, so I'm typing Judy here, no middle initial this time, enter, come back here, try Flash Fill, either with Control + E or the button, then you see what's happening, it overlooks all the middle initials so not so good there and let's go back to that previous layout where we had the D there. Another approach here might be let's put in a period here.

Let's say we want a period after the middle initial. We'll complete the entry and try Flash Fill and what do we get here? Well, not quite so good because we see trailing periods all the time, so that obviously doesn't work in our favor. We definitely want to undo that. Going back to where I typed the entry earlier, let's do Flash Fill and we see what's happening but as I double clicked, as I showed you earlier, there are some trailing spaces only behind those names that do not have a middle initial so what you might need to do sometimes after doing this is set up a new column, and this is of course a slight deficiency in Flash Fill, and now the reason we want to get rid of trailing spaces would be that if we're trying to match up names out of different lists, if one has a trailing space, another one doesn't, they'll never match so equal trim, left parentheses, point over here, Control + Enter, now we don't see the obvious difference, we won't see the obvious difference on many of these but we will have cleaned up the spaces.

When you do use functions, by the way, like this, I'm using the trim function here to clean up the data. When you use these, if you want to convert the formulas into their actual results, you can simply have the data highlighted and with the right mouse button, drag this temporarily elsewhere and then right back on top of itself so I'm gonna drag it up down. I'm using the right mouse button, up, down, let go of the right mouse button, copy here as values only so as I double click behind these names, you'll see there's no trailing space and that could be important in some situations.

All right, another small example here, I don't like the look of these because the letters were not capitalized. I'll type in one example here, 9C0K904, that's the way I want the data to look. Control + Enter, Control + E, that cleans up nicely and I'd throw away that data. Now, another common problem, city, state, zip. Here's a list of nearly all the zip codes in the US. If I double click the bottom edge, we're down to row 41,000 and I'd like to put these in separate columns so, imagine if I've got contact names next to these, I want to make sure that I can sort the list by state and/or zip, so right here I'm gonna type Boulder.

Now this time I'm going to type these going across, I'll tab over here, I'll type CO, tab over again, 80304, and one-by-one. Now hopefully, we should be able to do this but it won't work, Flash Fill becomes deactivated so we gotta do these one at a time but it certainly doesn't take long, how about Control + E, now I've got a huge list there. I'll double click the bottom edge, looks like it went all the way to the bottom and it does handle cities with two names. Here we see what's happening with Sullivan City and so on and right here, Flash Fill or Control + E, there we are.

Has our master list nicely split up into city, state, zip and it could not have been faster. We didn't have to worry about formulas here, we could throw away column P if we wanted to. Don't need it anymore since we have all the data this way. The reason column Q is wider is that somewhere in the list we've got some wide city names, you know, some California City, maybe, Rio Del Mar Vista, something like that maybe, or who knows. Great feature here, Flash Fill for cleaning up the data in a variety of different ways.

If it doesn't work for you the first time on a certain set of data, try it again, be persistent. Occasionally you run into issues here. Let me backtrack a little bit here with Control + Z. Here's a possible example here. I'm going to type instead here, Colorado Springs. Now the situation here is they're all caps, comma, now I'm not trying to break the system here but here's what could happen in the example here. I'm going to type CO and I'm thinking of the state. Now, if I do that only and choose Flash Fill, you see what's happening, we're getting the first two letters of every city.

Now, let me undo that and if I had typed right here, OH, and then highlighted both and clicked Flash Fill, it looks like it can't figure it out. Now, I'm surprised at that. I seemed to have seen that in the past and it doesn't seem to work. Maybe I'll do a third one here. Maybe by now Flash Fill is going to get the idea. Try it again, it still doesn't get it so I'm surprised at that. It seems like I've seen that in the past work but maybe not. So, I probably wouldn't do this this way but nevertheless, what if that were the first city? Why don't I type it this way, I'm going to type Colorado Springs, get rid of this data, click here, Flash Fill, that works, or does it? Oh, I misspelled Colorado, and I didn't do that on purpose so one more time here, there we go.

Type that entry, click here, Flash Fill, and we see what's happening. So now I'm on the right path. If I type CO over here, will it mistake it as being the first two letters of the entry or will it pick it up as the state? Let's try it again with Flash Fill and it didn't, it failed again so give it one more shot here with OH and maybe a third entry down here, KY and once again we'll see what happens here with Flash Fill and once again it fails the test so not perfect as we can see here.

I wasn't necessarily trying to break it here. I did want to point out that as powerful as this is, I'm a great fan of it, this doesn't work in this case and I wish it would and I think it should. The work around here, of course, would be relatively simple. I'll simply copy this down a few cells, let me get rid of this data first, press delete, take this entry here and just move it down a cell, using the shift key we'll move it down to there and now we'll try this over again and it should work just fine. This time we'll type OH and Flash Fill, it's working just fine so sometimes you have to manipulate the data to work around it.

Flash fill, great tool.