Excel tip: Split first name and last name into separate cells
Recently, I helped out a local charitable organization by printing some labels from an Excel spreadsheet that had a single column called “Name” that contained both first name and last name. City, state and zip were also in a single column. I offered then to clean up the spreadsheet by splitting the data into separate columns, but hadn't gotten around to it. This week I got email asking if I could do the clean up since they were going to use the data again. It's been a busy week, and I was planning on working on it this evening... a polite nag mail in my email when I got home prompted me to start working on it before I worked on laundry (this was okay because the laundry's not out of control... yet!)
I figured I would have to write a macro to do the deed -- I'm a macro newbie, but “how hard can it be?“ I thought to myself. However, I searched through help in Excel and VBA with a variety of search terms with no luck. Then, of course, I went to the web, but again I didn't have much luck. I thought I was either going to do them by hand or whine and beg to get Duncan to write a macro for me. I persisted, though, and finally found a reference to a “Text to Columns” command. That sounded intriguing. I played with it and once I figured out how it worked, the command was so simple to use that I still have time to write the post AND get started on the laundry -- I've already sent the spreadsheet off!
So I'm sure you're dying to know the details. Let's assume that column A contains both first name and last name separated by a space. Let's make it a little more real by adding a middle name and a dual surname.
Start by inserting two new columns after column A. Then select all the cells in column A that you want to split (in this case A2 through A4). On the Data menu, choose the option Text to Columns (which of course I'd never even noticed before). In the dialog that comes up, select Delimited and press Next. In the next dialog, select Space as your delimiter (and uncheck anything else that is selected) and press Next. Don't worry if the next dialog shows the preview as three columns, that's why you inserted two new columns rather than just one! (This can happen because of a middle name, or a non-hyphenated dual surname, as I've shown above.) Click Finish and answer Yes when it asks you if you want to overwrite. And viola, you have your data split into two or three columns.
Now, go back and do a little bit of clean up where there's data in the third column and then delete the third column, since you no longer need it, and you're done. You don't even have to remove the trailing space after first name, it got removed in the process!
Looking at this example, it might seem like more trouble than hand-editing, but imagine if you have hundreds or thousands of rows you need to convert -- it's a real timesaver, and no macro required!
Comments
Anonymous
July 08, 2004
And would you happen to know how to turn the feature off?
This is the most usefull feature that bugs me in the long run. After using it, I will have to close Excel than reopen it because it keeps trying to "help" me by doing more and more Text to column on each paste in the range!
I dont know if it saved me more time than it wasted me in the long run!Anonymous
July 09, 2004
Strange! I didn't see anything like that as I was editing. If you'll send me more detail in email along with steps to reproduce the problem, I will see if I can get an answer for you.Anonymous
July 11, 2004
Did he ever send the details of the problem?Anonymous
August 03, 2004
very interesting, but there is a problem - when you split the names into two or three columns using this method and there are variations of first+last and first+middle+last names within the column, it tends to insert the middle names of those who have them in the same column with the last names of those who don't have middle names, reserving the third column for the last names of those who have middle names. you end up with a mixed column -not good.Anonymous
February 07, 2008
PingBack from http://allen.alew.org/2008/02/07/split-first-and-last-name-into-seperate-columns-in-excel/Anonymous
August 03, 2008
PingBack from http://joel.greatvideonews.info/inexcelseparatelastnamefirstname.htmlAnonymous
August 23, 2008
PingBack from http://littleheath.net/blogs/e-learning/2008/08/23/excel-and-markbooks-some-hints-and-a-security-reminder/