Splitting First and Last Names

This is somewhat of a common problem for Purlem users. Purlem requires that the first and last name be split up in two separate columns. However, many people have a spreadsheet with the first and last names combined into a single column.

Fortunately, the solution is quite simple. I outlined the steps below to separate the first and last names into their own columns.

More of a visual learner?  Watch the video here.

1) Open the Spreadsheet in Excel

I’m using Mac Numbers in the example below.  But the process is more or less the same. In this example we have a single “Owner Name” column, with two different names separated by a forward slash.  The goal is the extract only the first of the two names, and then to split the first and last names into their own columns.

Screen Shot 2014-02-11 at 10.23.11 AM

2) Copy and Paste the Name column into a new spreadsheet.

Create a new spreadsheet in excel.  Then copy the entire name column from the old spreadsheet, into the new one.

Screen Shot 2014-02-11 at 10.24.14 AM

3) Save as CSV

If your using Excel, select File > Export > Save As > CSV (Windows)

Open the CSV file in a text editor.

Screen Shot 2014-02-11 at 10.25.01 AM

4) Find/Replace to Create new Columns

In CSV files, the comma represents a new column.  Hence the “Comma Separated Values.”

So to split up the name into it’s own columns, we simply want to replace every space with a comma.  In this example, we will also be replacing every forward slash with a comma.

Once commas have been added, Save the CSV file.

Screen Shot 2014-02-11 at 11.30.31 AM

5) Open CSV file in Excel

You now have all the names sepearted in their own column.  Since we only want to keep the first and last name, we can delete all other columns.

Screen Shot 2014-02-11 at 11.31.54 AM

6) Past the First and Last Names into Original Spreadsheet

Now you can copy out the first and last name columns, and past them into your original spreadsheet.  And that’s it!

Screen Shot 2014-02-11 at 10.31.46 AM

  • Les Hardy

    Just open the csv file in GenScriber and split the column. Takes only a few seconds, and much easier

  • Martin Thomas

    Great suggestions!

  • Chris

    alternatively you could use extractmails.com to process your list 🙂

  • Martin Thomas

    Never heard of extractmails before. Looks like a nifty tool 🙂