Join Dennis Taylor for an in-depth discussion in this video Splitting data into multiple columns, part of Excel 2016 Essential Training.
- In this worksheet called SplittingData, we're looking at two common problems that you might encounter when you're working with lists. Whoever prepared this list wasn't really thinking too much as the names were entered in column A, that later, we might want to sort this list by last name. And you can see how the names are put in, first name, last name. We cannot sort this list by last name in its current form. And also, depending on the size of the list and maybe it's quite large, we cannot sort this list by state.
Nor can we sort it by zipcode based on the current arrangement. As a general rule but nobody's stopping you, don't put city, state and zip together in the same column. You can't sort by state, can't sort by zipcode. And with names, even though you might not be thinking about it initially, keep in mind eventually you will want to sort by last name. Now there are a couple of approaches to this. In this movie, we'll be talking about a feature called text to columns. It begins, first of all, with the recognition we don't want to keep the names the way they are in column A.
We want a way to sort this data on the basis of last name and we can't do that until we separate these into separate columns. We might want to rejoin them, maybe, maybe not. But let's focus on the idea that we want to separate these in separate columns. Now, you could say this is rigged a little bit, nobody has a middle name, we don't see any middle initials in this list. If we did, it would take just a bit longer. But the feature we'll be using, after selecting the column, is found on the Data tab in the ribbon.
It's called Text to Columns. But if we start that right now, we've got a problem because we don't have any additional columns in which to split this data out. So let's insert a few columns. Now, even though we know our data reasonably well, let's say we know that all the names are in two parts, let's put in two columns just to play it safe. Highlight columns B and C, right-click and Insert temporary new columns. Then click column A or simply select the cells, either way.
Text to Columns, the description's quite lengthy. "Split a single column of text into multiple columns. "For example, you can separate a column of full names "into separate first and last name columns." That's exactly what we're doing here. Click that option. This brings on the screen the Convert Text to Columns Wizard - Step 1 of 3. Often, you don't need all three steps. The key choice here in the initial step one is Delimited. "Characters such as commas or tabs separate each field." In this case, it's a space.
But that's what we want, Delimited. Fixed with would mean every four characters exactly or every eight characters. We don't have that situation here, the names are of different length. Delimited then Next. Now if you've used this recently, maybe you used Tab, maybe you didn't. That's not getting in the way here, we can pretty much ignore it. Below, we see a data preview but we haven't chosen anything yet, how about Space? In other words, what separates the names the way we would like to see this? Space and the preview below shows us what we're about to do.
We're going to split the names. Might take you a long time to scroll through this, you don't necessarily have to do that. It looks like this is going to work. We're on step two, we need only click Finish, we're done here. The data has been split into two separate columns. Now, one choice is simply to say, keep them this way, relabel this, call it First Name, relabel this, call it Last Name. Then we'd get rid of the empty column if you had an extra one. Right-click and Delete. Readjust the column widths here, put in the appropriate labeling and then we can sort by last name.
So that's commonly done. So I'll change this here to be First Name and over here, Last Name. Now is it better to put these back together into a single column? Santos comma Evan, Wiley comma Stewart, Powell comma Brittany and so on. We could do that. It might save us a little bit of horizontal space on the screen. But there's certainly nothing wrong with what we see here. You might eventually add another column for middle initial. It just depends upon the individual needs. But this is certainly acceptable here and we can sort our list by last name.
And so what I can do right now, with the active cell here and all of our columns back together, on the Data tab I can simply click the single A-Z button and now they're sorted by last name. And that's handy and fast. Now, we've got a similar problem in column D. City, State Zip. Now some of those city names are two names. Here's Huntington Beach and there might be a California city named Marina Del Rey Mar Vista or something like that. Some city names might have four or five names so we really can't split these by space.
State and the zipcode we separate by space. But it's the comma that we're looking for here that would allow us to split this data to at least isolate the city. And what I'm getting at here is the idea, we might have to use this feature multiple times. So let's insert four or five new columns. We can always get rid of the empty ones that we don't need. Drag across right here, right-click and Insert. Once again, we'll use this feature called Text to Columns. Delimited, Next.
Now we don't want to use Space as the separator, we can see in the preview already, La Palma, KS looks different than the others there. As we move up and down, there's Huntington Beach. See how Beach comes into a different column here, into the state column and so on. So we don't want Space here, uncheck that. Click comma and look at the preview. It looks like it's isolating the cities properly. And as we move up and down, we see those multiple name cities here and there but the state and the zip are off to the right. So this won't separate those just yet.
We want to use comma, let's do a Finish. Now sometimes, you'll get this message even if there's no data there. Let's click ok and what we should do is just to make sure, click in column F. Is there any data here, really? Ctrl + down arrow, we can press. Goes all the way to the bottom, nothing there. So there really wasn't anything there. So, we've taken care of part of this. We can double click the boundary between D and E. We've isolated the city, we see it here. State and zipcode are separated by a space but you might notice there's a leading space there.
So let's do this again, same feature. We might end up with an empty column temporarily but we can deal with that. Text to Columns. By the way, it is possible here that some of those zipcodes might be the seven digit zipcode but that wouldn't stop us here, it wouldn't get in the way. We're going to be using Space now, only Space. Not Comma but Space. And once again, the preview. Seems to be doing what it's supposed to do. We will have an extra column temporarily here. It'll be empty. We can get rid of it.
Finish. And once again, we get this message. Click again, just to make sure we could go off to the right. Click in this column out here, did we get any data out here somehow? Ctrl + down arrow, nope. Ctrl + up arrow. So, first order of business is to bring this back together a little bit. Readjust the column widths, see what we've got. We've got an empty column E, we can delete that. Right-click, Delete. Looks like column G is empty. Right-click, Delete.
So, city, state zip. Looking the way it should. Now in our list here, if we want to sort this by State and within that by City. We would sort by City first. We can click in column D anywhere, click the A-Z button. We're sorting our list by City but they are in different states so then let's click in column E, any one cell and click A-Z and there we have our sort. So here are all the cities within Alaska then all the cities within Alabama and so on.
Now we're able to work with the data efficiently. Anytime you're working with data that has city, state zip make sure they're in separate columns. If not, you'll have to use the features we just saw here using, on the Data tab, Text to Columns. And earlier, we saw how to do that with the names as well.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros