Hey welcome back to the Mr. Excel netcast, I am bill Jelen. Today’s question sent in from Dianne from Michigan and boy, Dianne is very specific in this. She says that she has some data names, first name, last name that runs from G5 to G400. She wants frist name in column G, last name in column H.
All right 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 data sect, 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, because we don’t have the first name, last name and then two, all right so you always have to sort this data by these fields out here to figure out what didn’t match, all right so like here, Marie Francis Bell. Marie Francis is probably the first name and Bell is the last name. Ruby Aguilar Perez, that’s probably a—should be a hyphenated last name. Jose Graves III, that should belong there and I—you know we’ll sort these first, I sort by this one over here, A to Z, good, there were none of those, now sort over here, A to Z and then I just fix those, all right so I retype things.
So anytime you use Text to Columns, beware 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 equal left, went the left to that name, comma find, a space within the name, that’s going to return the position where the space is a direct one from it and we will not check that out, copy it down, works out well and then what we need here is equal mid of that and we want to start at the length of the first name and put in an impossibly large number like 50 and to get rid of the leading and trailing spaces, start this with trim, close parenthesis. Copy that down. 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 could use, on the Home tab Copy and then Paste, Paste Values, there. Are you still want to sort these data though to look for any errors? So I'm going to go to data this time sort, Z to A. Z to A, so right there someone with only one name caused problems with our formulas 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 checks and then of course we wanted our first name in column G so I’ll make sure to delete this.
All right so it sounds like a really easy question but of course as you get into this, there’s a couple of 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 we go, Dianne thanks for sending that question in, thanks to you for stopping by, we’ll see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services