Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
This list of names and locations has a couple of problems with it. And you could imagine a real life situation with more columns to the right, more about the contact information. If we want to sort this list by last name, we can't do this in the current form. If we want to sort it by State we can't do that either, and we cant sort it by zip code. Essentially, we've got the same problem in two separate columns. What we really need to do here is to split the data into multiple columns. Now you might want to, put that data back together again. That's fine. But let's focus first on column B.
Rather than retyping this, and you could imagine how tedious that could be with thousands of records. Let's select the data and on the Data tab simply use Text to Columns. The key word here is Delimited. There is a character that separates these, and it's pretty apparent that the comma is what terminates the name of the city and of course some cities have two names. Wheat Ridge, here we go, Walnut Creek, San Francisco and here and there you've three names, four names. Marina Del Rey, Mar Vista, maybe something like that.
Step one choose Delimited > Next. Comma is the key indicator here. This will take care of isolating the cities, but not the state and zip, so we will essentially do this twice. At this stage, simply click Finish and you see what's happened here. Readjust the column width and now we've got this data here and we'll do this all over again, but this time the separator is a space. Text to Columns > Delimited > Next, you can leave the comma there, it doesn't hurt, put in Space.
The preview down below is pretty much telling the story except sometimes you do want to scroll up and down. You might find some odd things in there. We don't in this case. But and it looks like we'll get an extra column, we can handle that, simply Finish again. Probably Delete column C or move the data over, either one. And we're in business here and we've got this cleaned up. And now we can sort the data by state and within that by city or sort it by zip. And the same idea, of course, in column A, but we would need an extra column.
Now the example is fairly simple in that nobody has middle initial. If we do have middle initials and middle names or names like John Henry Van Der Horse, IV, we're going to need a lot more columns in this. So, do give yourself plenty more columns if needed. In this case we only need one extra one. Same idea, real fast here, click column A, Text to Columns > Delimited > Next, Space is the key separator here. Finish. There we are, like that.
Probably re-label these and we're okay. If you prefer to put these back together, that's a different issue but real fast here. We can reconstruct these names in the following way, =, and simply pick this name put in ampersand, the & symbol. Following that last name, you probably would want within double quotes, a comma and a space and the first name. It looks like this. Double-click it to copy down. If at the same time you want to adjust the Uppercase/Lowercase just put the word Proper in front of this, and that will make it look even better.
Good enough there we go. So you see how that's looking. And at some point, as always one of the tips we use frequently, if we want to keep this data and throw away the rest, we can use the right mouse button here, take any edge, click and drag it, for example up and down right on top of the cell, with right mouse button, Copy Here As Values Only, move this heading over, get rid of these columns. So some quick easy ways to split data in to column so we can work with our data better.
We can now sort this list by last name, by state, by city, by zip easily, and it reminds us of a general rule on database like data. Make each column contain the minimum amount of information and you usually are better off if you start with that idea in mind.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70476 Viewers
80 Video lessons · 127621 Viewers
52 Video lessons · 62513 Viewers
59 Video lessons · 48221 Viewers
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.