Splitting Names into columns using Excel or Word

As a startup entrepreneur you get lists of name in Excel spreadsheets and in most cases there is one column with the first name and a column for the last name, but on occasion both names are in the same column. Or you get the City, State, and Zip in the same column and can cause headaches with having to sort this data.

Along comes MS Excel 2008 which has a data trick that helps clear up your list of names or any list for that matter. Let’s take a look at how we can do this.

Here’s an example of what I mean.


MS Excel. As you can see from the image above, this is a typical example of what you might get, or the first name and last name positions are changed. Either way, you have the full name in a column that you’d like to split out into two for both sorting reasons and for mail merging your data in form letters.

Start by opening up your file into an Excel sheet with the names in the column. Here’s the steps.

1. Highlight or select the data in the cells you want to split.

2. Select the menu Data > Text to Columns and begin going through the necessary steps to split the data into two columns.

3. How is the data delimited? When you select the Text to Column menu you’ll have two choices to select from: a delimited file that has either tabs or commas separating both names, or a fixed column width, i.e. 25 characters for example, if you get your data from a large database. In most cases you’ll get a delimited file where either the comma or tab will be the deciding factor of your file. There are other defining “Text qualifiers” that can help separate your data. In the second image you can see that Excel has given you a view of what your data will look like when you have defined how the text is handled by Excel. If you need to change your mind, just hit the back button and redo your selections until you get it correct. This is what it should look like once you have done the appropriate steps.


4. Follow through until you arrive as how you want your data to be split and then hit the “Finish” button.

You’re done.

As always, it depends on how large your data set is, whether the list of names is around 20 you could do it manually and would be quicker, but the larger your data the more you’ll need the tools to speed up the process.

Runner Up: Search and Replace: If you are not planning on importing the data into a database or contact management program and have a text document, you can always open up and do a “search/find and replace” and replace the comma and space, spaces, or tab characters with something that you need instead.

The Mac application TextWrangler by Bare Bones, a free app from them, which does a LOT more to just plain text for you.

Enhanced by Zemanta

Leave a Reply