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)), “”),””)
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.