Separate Name into Multiple Columns in Excel

Many of the files that I work with come from different external vendors and are going to be used for marketing efforts of some kind. Generally, it's going to be a direct mail, an email or even a targeted online ad. Each of these vendors have a different file format that they use to export their data. A lot of times they will export the name of the contact all in one field. This can be undesirable if you are trying to target someone using their first name in a marketing piece.

So today, we're going to talk about how to split this one field into many. I'll be working with a very small set of data (for example purposes only), but I think you can apply these same methods to 10,000 records or possible more. Larger than that and it might be better to import the file into and use SQL to do the work. That's another article though.

So let's say your file looks something like this:

Insert enough rows to account for possible pieces of the full name. So for instance in cell A4 you see Ted Robinson III which will create 3 columns once we break it into first name, last name and suffix. So you would insert 3 columns. As a note: If you have a large data set it might be easier to add an identifier to each record into this file and copy the full name column and identifier into a separate file to do the following steps so you don't overwrite any of the other data in the columns to the right. So my file looks like this:

Now select the full name A2:A7 and do a text to columns (this should be under the data menu). You'll see the following screens. Select delimited, next, space (uncheck all others) and finish.

Then you're file will look like this:

After that you just need to sort to get the right names into the right columns. Remember to sort the entire file if you haven't put the name in it's own file.