In today's episode a caller asks how to split names that are in one cell into first name in column A and last name in column B. Episode 395 shows how to do this trick, but then the annoying gotcha that will happen for the rest of the day.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Monday, we have a call today, if you have a call, call 866-581-0221, leave your message on voicemail, and we'll get to your call.
Today's call: “Hi Bill, your programme is great, uh, the podcast I mean.
Hey, I would like to know if you have, let's say uh, you report(?) a database, and then you have names and last name all together in one row.
How could you split that on two rows?
Thank you very much, have a good day, bye!” Hey, that's a great question.
On Friday, I showed how to join first name and last name together.
Now today, coincidentally, this call asks how to split them apart.
OK, the first thing that we have to worry about here, is most of the time we're going to get two names, first name and last name, but the trick is actually going to break things up at every words.
We have to worry about the fact that someone might have 3, 4, or 5 names.
Right now I have first name and last name in column A, and an ID number in column B.
Just to be safe, I'm going to insert a whole bunch of columns here, so that way I make sure I don't overwrite the ID number.
Select my original data set, that has first name and last name separated by a space, and then from the Data menu I'm going to choose Text to Columns.
Now I have two choices: either Delimited or Fixed-width, in this case, we're going to say our data is delimited by a space.
I choose Delimited, click Next, and on Step 2, initially it starts out with a default on a Tab, I have to add a Space here.
The box “Treat consecutive delimiters as one”, that's important, in case we ever have someone typed first name space space last name, click Next.
You might be tempted to change this to text unless you have leading zeros, I wouldn't do that, just leave it as general.
Finally, click Finish, and Excel will convert our data, breaking it up at every space.
Now, most the time we're going to have first name in column A, last name in column B. Sometimes though, we might have extra names in column, C, D, or E. And what you can do is go to column C, hit the End key, and then the down arrow, and it will jump to any cells that had an extra name, and you just pretty much have to go through and fix these.
Now, it's easier fix the few of them rather than all of them, and then, will take you down to the next one, etc.
And then once you've made sure that you've gotten rid of all the extra data in column C, D, and E, you can delete those extra columns.
And again, I just inserted the extra columns to make sure that I didn't accidentally overwrite any of the ID fields, if we had something there that was 3, 4, or 5 names, it would take up that many columns.
Now, here's one really frustrating gotcha that's going to happen for the rest of the day after you use Text to Columns.
Let's say you have some text in an email or Notepad or something like that, and you copy the text and you plan on pasting it to Excel.
Normally, every line in Notepad gets pasted to a new row in Excel.
But today, since we've previously used text to columns, and said that space is our delimiter, until we close and reopen Excel, Excel will remember that setting.
And when we paste the data from Notepad, instead of getting every line in a new cell, we get every word in a new cell.
Very frustrating behavior, and it will happen until you close Excel after using Data, Text to Columns.
And again, it's all based on what you said in Step 2 of the Text to Columns wizard, very annoying gotcha, something to watch out.
You know, you might see this behavior happen one day, and then not happen the next day.
The trick is that earlier, in the first day, you used the Text to Columns, and Excel does remember that setting, and it's automatically parsing every new text value that you paste into Excel.
Crazy!
Anyway, thanks for the call, I appreciate the call, how to separate first name and last name into two columns using Data, Text to Columns!
We’ll see you tomorrow, Tuesday is our Excel 2007 Tuesday, I'll show you another new trick coming into new Excel!
Monday, we have a call today, if you have a call, call 866-581-0221, leave your message on voicemail, and we'll get to your call.
Today's call: “Hi Bill, your programme is great, uh, the podcast I mean.
Hey, I would like to know if you have, let's say uh, you report(?) a database, and then you have names and last name all together in one row.
How could you split that on two rows?
Thank you very much, have a good day, bye!” Hey, that's a great question.
On Friday, I showed how to join first name and last name together.
Now today, coincidentally, this call asks how to split them apart.
OK, the first thing that we have to worry about here, is most of the time we're going to get two names, first name and last name, but the trick is actually going to break things up at every words.
We have to worry about the fact that someone might have 3, 4, or 5 names.
Right now I have first name and last name in column A, and an ID number in column B.
Just to be safe, I'm going to insert a whole bunch of columns here, so that way I make sure I don't overwrite the ID number.
Select my original data set, that has first name and last name separated by a space, and then from the Data menu I'm going to choose Text to Columns.
Now I have two choices: either Delimited or Fixed-width, in this case, we're going to say our data is delimited by a space.
I choose Delimited, click Next, and on Step 2, initially it starts out with a default on a Tab, I have to add a Space here.
The box “Treat consecutive delimiters as one”, that's important, in case we ever have someone typed first name space space last name, click Next.
You might be tempted to change this to text unless you have leading zeros, I wouldn't do that, just leave it as general.
Finally, click Finish, and Excel will convert our data, breaking it up at every space.
Now, most the time we're going to have first name in column A, last name in column B. Sometimes though, we might have extra names in column, C, D, or E. And what you can do is go to column C, hit the End key, and then the down arrow, and it will jump to any cells that had an extra name, and you just pretty much have to go through and fix these.
Now, it's easier fix the few of them rather than all of them, and then, will take you down to the next one, etc.
And then once you've made sure that you've gotten rid of all the extra data in column C, D, and E, you can delete those extra columns.
And again, I just inserted the extra columns to make sure that I didn't accidentally overwrite any of the ID fields, if we had something there that was 3, 4, or 5 names, it would take up that many columns.
Now, here's one really frustrating gotcha that's going to happen for the rest of the day after you use Text to Columns.
Let's say you have some text in an email or Notepad or something like that, and you copy the text and you plan on pasting it to Excel.
Normally, every line in Notepad gets pasted to a new row in Excel.
But today, since we've previously used text to columns, and said that space is our delimiter, until we close and reopen Excel, Excel will remember that setting.
And when we paste the data from Notepad, instead of getting every line in a new cell, we get every word in a new cell.
Very frustrating behavior, and it will happen until you close Excel after using Data, Text to Columns.
And again, it's all based on what you said in Step 2 of the Text to Columns wizard, very annoying gotcha, something to watch out.
You know, you might see this behavior happen one day, and then not happen the next day.
The trick is that earlier, in the first day, you used the Text to Columns, and Excel does remember that setting, and it's automatically parsing every new text value that you paste into Excel.
Crazy!
Anyway, thanks for the call, I appreciate the call, how to separate first name and last name into two columns using Data, Text to Columns!
We’ll see you tomorrow, Tuesday is our Excel 2007 Tuesday, I'll show you another new trick coming into new Excel!
