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!