Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you have data that need to be separated like splitting first names from last names, it's hard to think of a better tool for the job than Excel. And even if I'm using other software for project, I'll still bring that type of data into Excel temporarily just to use its ability to separate data. Well, if you take a look here in Column A we have people's names and we have first name and last name in the same column and what we want to do is we want to split so we have first name in one column and last name in another column. Now before we do that we have to insert a new column and I'll show you why in a second.
Put your mouse pointer on the header for Column B, so you have a down pointing arrow. Click the right mouse button and Insert, so now we have a new column. The reason we're doing this is that we need space to put the last name. So we are going to wind up with the first names in Column A and the last names in Column B. If we hadn't inserted this additional column, we would've wiped out what is in the Department column next to it. So let's select the names. Now, you can click and drag down if you want. What I find this easier is to press Ctrl+Shift and then the Down Arrow key so that select all the way down to the bottom.
Let's scroll up. Leave it selected, go to the Data tab and in the Data tab, roughly in the middle here choose Text to Columns. This brings us into the Text to Columns Wizard and we want to make sure that our data here we have Delimited selected, not Fixed width. Click Next and our data are separated with spaces. We don't have any commas or tabs or anything and you can see it already gives us a good preview. Click Next and if we wanted to exclude a field or if we wanted to change anything like one of these columns are supposed to be a date, we can do that, but we don't have to do any of that right now.
So just click Finish and there we go. If you want, you can take that name, auto fill it. So maybe we can change that to First Name and just press Tab, Last Name, and there we go. You can click through here, click though here and you can see up in the Formula bar that this is all separated. So if you watch the movie on importing data, this exercise may have seem kind of familiar because Excel uses the same wizard, whether you're importing data from scratch or if you're working on data that you already have in your worksheet.
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.