Splitting columns of data within Apple Numbers (iWork)

I had a client today that wanted to move their email list, of maybe 1,000 addresses, over from Aweber to Constant Contact (sign up for a FREE 60 day trial). The process was pretty simple. Aweber allows you to export your list as a CSV file, and Constant Contact allows you to import the list very easily. The only problem? Aweber stored each subscriber’s name as one Full Name instead of separating the name into First Name and Last Name, the method Constant Contact uses.

I don’t use Apple’s Numbers software (their much better alternative to Microsoft Excel – available for just $20 in the Mac App Store), but I knew a little bit of time was going to be spent in there today. It took me a while to figure out how to take the opened CSV file and break the Full Name column into both First Name and Last Name. In the end, the steps to make that happen were so simple that I was kind of shocked more people had shared the process online.

Before you do anything, I recommend making a copy of the original CSV file and editing your copy. Just in case!

Assuming your combined Full Name is in Column A:
TO EXTRACT FIRST NAME FROM FULL NAME COLUMN A, the formula you would use is:
=IF(NOT(ISBLANK(A)), IFERROR(LEFT(A,FIND(” “,A)), A), “”)

TO EXTRACT LAST NAME FROM FULL NAME COLUMN A, the formula you would use is:
=IF(NOT(ISBLANK(A)), IFERROR(RIGHT(A,LEN(A)-FIND(” “,A)), “”),””)

INSTRUCTIONS:
You would want to create a new column for the first name, click on the first cell (in the new column, to the right of the full name in column A) and paste the formula for First Name above. Like me, you’ll then be confused about how to apply that formula to the entire column (the rest of the 999 names that aren’t yet separated). You’ll see a small circle on the bottom right of the box when you select the first name that gets created after entering the formula. Click and drag that small circle down the rest of the new column, and you will see every first name from column A separate and move over. Repeat this same process for Last Name in another name column, using the second formula listed above.

Hope this saves you a good 45 minutes, after I wasted them today trying to figure this out, LOL.

  1. This is quite impressive Gabe. It’s certainly a geeky post, and therefore I love it. But I’m intrigued, you said that “in the end, the steps to make that happen were so simple…” It is a simple process, but how did you come up with the formulas? They don’t seem simple to me at all. Is that where a decent chunk of your 45 minutes went into?

    I must also add, I tested them out and it almost worked properly, I just found I needed to replace the smartquotes around the ‘space’ with standard quotes. Whether that’s an issue regarding smart quotes, or if that in turn caused Numbers to link the cells into the formulas I don’t know… but neither of them worked until I deleted and manually typed the quote after the space (just before ‘,A’ in each formula).

  2. I tried your commands and got errors, so I changed the “A”‘s to “A2″, which was the cell with the name that I was splitting into B2 and C2. It still didn’t work, so I researched the commands you used and created a code that did work for me. Here it is:

    If the First and Last name is in cell A2, then the first names are: =LEFT(A2, FIND(” “,A2))
    The last names are: =RIGHT(A2, LEN(A2)−FIND(” “, A2))

    • James

      After I have split the single column containing names like “John Smith” into two columns contain a first and last names, how do I delete the original column??

    • James

      How do I delete the original column after successfully splitting its content into two new columns?

Leave A Comment?