Diane from Michigan wants to split a column of names into two columns. Episode 1145 shows two approaches and the problems with each.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Diane from Michigan, and boy, Diane is very specific in this.
She said that she has some data names, first name last name, that runs from G5:G400, she wants first name in column G, last name and column H!
Alright well, you know, there's a problem with this.
I mean, what we're going to do is insert a couple of columns here, and then select the whole dataset, Ctrl+Shift+Down arrow.
Go to the Data tab, choose Text to Columns, in the first step choose Delimited second step Space, uncheck Tab, and click Finish.
But anytime you do this, so we now have First Name, Last Name, and then Hmmm?
And then Hmmm2!
So you always have to sort this data by these fields out here to figure out what didn't match.
Alright, so like here, Mary Frances Bell, Mary Frances is probably the first name, and Bell’s the last name.
Ruby Aguilar Perez, that probably should be a hyphenated last name.
Jose Graves III, that should belong there.
And I, you know, will sort these first, I sort by this one over here, A-Z, good, there were none of those, now sort over here, A-Z, and then I just fixed those, alright, so I retype things.
So anytime you use Text to Columns, be aware that you're going to be going through a little bit of pain.
My other way is to build this with a formula, so First Name and Last Name, and I use the FIND.
So =LEFT, want the left of that name, comma, find a space within the name, that's going to return the position where the space is, -1 from it, and we get Wilma, check that out, copy it down, works out well.
And then what we need here is =MID of that, and we want to start at the length of the First Name, and put in an impossibly large number like 50.
Alright, and to get rid of the leading and trailing spaces, start this with TRIM, closing parenthesis, copy that down.
Oh, off by one, LEN(H5)+1, there we go, copy that down and we're good.
Still, a couple of things we want to do, we want to make sure to convert these formulas to values, of course.
So, you know, we can use on the Home tab, copy, and then Paste, Paste Values, right there.
You still want to sort this data, though, to look for any errors, so I'm going to go to Data, this time sort Z-A.
So right there, someone with only one name caused problems with our formula, so you want to fix those, again, manually.
So, no matter what you're going to do, you're going to have to be careful, to do a couple of manual checks.
And then, of course, we wanted our first name in column G, so I’ll make sure to delete this, alright, so it sounds like a really easy question.
But of course, as you get into this, there's a couple of, well, relatively easy approaches, both of which might have problems because of people that have either one or three or four or five names.
So, there you go, Diane, thanks for sending that question in, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Diane from Michigan, and boy, Diane is very specific in this.
She said that she has some data names, first name last name, that runs from G5:G400, she wants first name in column G, last name and column H!
Alright well, you know, there's a problem with this.
I mean, what we're going to do is insert a couple of columns here, and then select the whole dataset, Ctrl+Shift+Down arrow.
Go to the Data tab, choose Text to Columns, in the first step choose Delimited second step Space, uncheck Tab, and click Finish.
But anytime you do this, so we now have First Name, Last Name, and then Hmmm?
And then Hmmm2!
So you always have to sort this data by these fields out here to figure out what didn't match.
Alright, so like here, Mary Frances Bell, Mary Frances is probably the first name, and Bell’s the last name.
Ruby Aguilar Perez, that probably should be a hyphenated last name.
Jose Graves III, that should belong there.
And I, you know, will sort these first, I sort by this one over here, A-Z, good, there were none of those, now sort over here, A-Z, and then I just fixed those, alright, so I retype things.
So anytime you use Text to Columns, be aware that you're going to be going through a little bit of pain.
My other way is to build this with a formula, so First Name and Last Name, and I use the FIND.
So =LEFT, want the left of that name, comma, find a space within the name, that's going to return the position where the space is, -1 from it, and we get Wilma, check that out, copy it down, works out well.
And then what we need here is =MID of that, and we want to start at the length of the First Name, and put in an impossibly large number like 50.
Alright, and to get rid of the leading and trailing spaces, start this with TRIM, closing parenthesis, copy that down.
Oh, off by one, LEN(H5)+1, there we go, copy that down and we're good.
Still, a couple of things we want to do, we want to make sure to convert these formulas to values, of course.
So, you know, we can use on the Home tab, copy, and then Paste, Paste Values, right there.
You still want to sort this data, though, to look for any errors, so I'm going to go to Data, this time sort Z-A.
So right there, someone with only one name caused problems with our formula, so you want to fix those, again, manually.
So, no matter what you're going to do, you're going to have to be careful, to do a couple of manual checks.
And then, of course, we wanted our first name in column G, so I’ll make sure to delete this, alright, so it sounds like a really easy question.
But of course, as you get into this, there's a couple of, well, relatively easy approaches, both of which might have problems because of people that have either one or three or four or five names.
So, there you go, Diane, thanks for sending that question in, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
