Excel Tip: What’s The Easiest Way to Split One Column into Two

Let’s say you have a column that contains a list of people’s full name and now you want to split them into 2 columns for First Name and Last Name. What’s the easiest way in Excel that I can do that? I have to confess that until I learned this tip, I have been stupidly using some string function to manually separate them. Not much a big deal but it was stupid.

Excel has a much better way built right in. And why not taking advantage of it?

Here is the list of people I have in one column with their full name. Yes, I am teaming up some of the super stars.

Excel column with full name

And here is how to easily split them into 2 columns, one with their first name, and one with their last name.

Highlight the column A that contains all the names, switch to Data tab, and click Text to Columns button.

Excel - text to columns

A Convert Text to Columns wizard pops up.

Step 1 of 3: pick Delimited, which is set as default, and click Next;

Step 2 of 3: select the type of delimiters. In this case, select Space. And go to Next. Of course, if you have a column that uses other delimiters, such as tab or comma, you can select them in the list accordingly. You can even select multiple of them, if that is the case.

Excel - convert text to columns wizard step 2

Step 3 of 3: select the data format, and click Finish. If you want the data on the different column, you will need to select them in Destination field. Or, the original full name column will be overwritten.

Excel - convert text to columns wizard step 3

And bang, here we go. Yes, I will still have to manually rename the title of the columns.

Excel columns separated

Kent Chen

Microsoft MVP, IT Professional, Developer, Geek, and the co-founder of Next of Windows.

Last updated: 08/04/2014

Posted in: Tips & Tricks
Discover more: ,

Leave a Reply

Notify of
avatar
wpDiscuz
Today's Top Picks for Our Readers:
Recommended by Recommended by NetLine