Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this worksheet SplittingData, we've got our customer list, it's over 200 names and we'd like to be able to sort this list by the last name. But because the names are in first name-last name order in column A, we can't do that. This is a common problem, by the way. Also, if we want to sort our list by State, we cannot do that. Look at the entries in column D; City, State, Zipcode all together in one column. There are two methods for adjusting data here. One has been around for a while, it's a Text to Columns feature on the Data tab and a new one in Excel 2013 called Flash Fill will quickly allow us to readjust data as well.
Let's focus on column D first, click column D. Before actually splitting this data into separate columns, make sure we've got some empty columns to the right and we do here. The command on the DATA tab is Text to Columns; Split a single column of text into multiple columns. Note how they do make reference here of separating a column of full names into separate first and last name columns. We're going to be doing that in column A using Flash Fill. We could use this feature as well; but we're going to use it here. We want to split this data into different columns.
The Convert Text to Columns Wizard begins with a choice Delimited or Fixed Width. If we look at the data in column D, it's not every six characters or every eight characters in each case where we see a city name ending, in other words it's not a Fixed width. Delimited means characters such as commas in our case or tabs separate each field. So we want to choose Delimited, then Next and a Delimiter and these might not be checked, they could be checked based on the last time you've used this, so these might or might not be checked.
We don't need to see Space there, in fact that would be wrong to choose Space, because we see spaces between for example, San and Diego, Dana and Point and so on. It's just Comma that we care about. The Preview below this, going to keep an eye on it, scroll a little bit. If we're using Space I think you'd see we'd have a problem there, just use Comma and then Finish. And we've taken care of the Cities, we've isolated them based on the commas and we don't see the commas anymore. Now let's tackle column E. Looks like we might have a leading space but that shouldn't be a big problem.
We'll go right back into Text to Columns, this time Delimited then Next and it is Space that we want to choose now. It doesn't hurt to leave Comma checked; we don't have them there anyway and we see what's about to happen here based on this Preview, Finish, good. Column E is empty, we'll just Right-Click and Delete it, re-adjust these column widths and we are all set. We will be able to sort this list now by State or by Zipcode. Take a look at column A though, we still have a problem here, we cannot sort the list alphabetically by last name, we need to split these names into separate columns.
And although we could use Text to Columns, let's put in some new columns here, I'm going to drag across columns B and C and use the feature new in Excel 2013 called Flash Fill. So, in cell B7 here I'm going to type Jim. Now I'm going to type Lawrence and as I type the letter L, look what happens. Excel senses that I'm going to type only the first name and it fills in all the other first names below, they're kind of gray, you might not see them so easily, I'll simply press Enter and we've got all of our first names in place.
And then in C7, right here I'm going to type Duffy, Enter and as I type, T that's Lawrence Tibbett there, that T for the last name. As I type the letter T, we see what's happening; once again we'll press Enter. So ultimately what we're going to do here is get rid of column A, we'll get to that. Meanwhile we will re-adjust these columns, put in our appropriate headings for First Name, Last Name and we'll be able to sort by last name now. Now although we don't really need to put the name together we can do that also with this feature. I'm going to Right-Click column D and insert another new column and suppose what we might have, because it will be more efficient space-wise, to contain the names Duffy, Jim; Tibbett, Lawrence and so on.
So I'm going to type Duffy, Jim Enter and now as I type T, immediately Excel senses what I'm about to do, I'll press Enter and we're all set there. And so what I might do ultimately, I'll just move the Name over here and then delete columns A, B, C, Right-Click and Delete. So now we've got a list where we can sort by last name, in fact the column doesn't even have to be that wide anymore. We can sort it by the last name, we can sort by City; we can sort by State.
So the Text to Columns feature does by the way include some additional options that you might want to explore particularly in some of the other steps here regarding dates. But, if it's simply a question of splitting data in the columns, we can use either this feature or as we also saw the ability called Flash Fill that allows us to quickly do a little bit of typing and have Excel fill in the columns for us automatically.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.