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.
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.
3) Save as CSV
If your using Excel, select File > Export > Save As > CSV (Windows)
Open the CSV file in a text editor.
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.
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.
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!