It’s 5:07 pm on Friday, and just as you’re about to step out the door, you get an email from Mr. Always-Launching-Campaigns-At-The-Last-Minute (let’s call him Bob for short). “Hey, I’ve got this promo email going out on Monday, can you upload this contact list export into {insert your Email Blasting Tool here}? Thanks, have a good weekend.
“Have a good weekend my ass,” you think. Grumbling, you sit down to convert the Excel file to a .csv for import. Except, it’s huuuuuuge. And has first name and last name in the same column.
Okay, well, no problem. Let’s delete the unnecessary data columns and split the name column into two columns, using the space character as the identifying separator. Easy, peasy, lemon squee –
Your Excel sheet now looks like your CRM got sick and puked all over it. At least, you hope that’s puke.
It seems that a bunch of the contacts have middle initials in their “Name” field. Oh, and some of the contacts are splitting in weird places.
“DAMN YOU, BOB!”
Introduction
Contact name fields often present the biggest challenge when cleaning up source files for import. In this article, we’ll walk through the process of splitting up first name from last name, cleaning out middle initials, and removing hidden characters.
Important Note:
This article uses Microsoft Excel to manipulate the source files. The formulas below assume that the name data you are trying to manipulate is separated by spaces and has the first name first and last name last (e.g. Lewis D. Carroll) and that you’re spreadsheet has a header row (i.e. the data you want to manipulate starts on row 2.
The set up
Create three new columns next to the original name column. Column B is for the “cleaned up” name, column C is for first name, and column D is for last name.
Trim extra spaces
The first thing you want to do when cleaning an Excel file is to make sure trailing spaces, line breaks, and non-breaking spaces. Use the following formula in column B to remove extra spaces and funky hidden characters.
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
Make sure you do trim extra spaces before doing anything else. The formula we’re using to split first name from last name figures out where to split the name based on spaces, and if you don’t trim extra spaces first, you may get weird results.
Split first and last names into separate columns
Now that you’ve chased out any extraneous spaces, it’s time to split that “full name” into first name and last name, getting rid of middle names or initials.
In column C (the new “first name” column you created at the beginning of this article), use the following formula:
=LEFT(B2,FIND(" ",B2,1)-1)
In column D (the new “last name” column you created at the beginning of this article), use the following formula:
=IFERROR(RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2,SEARCH(" ",B2)+1))), RIGHT(B2,LEN(B2)-FIND(" ",B2,1)))
Ta-da! Now you have your first name and last name data split out nicely into their own columns. Apply the formulas to the rest of the rows, and you’re good to go.
Things to keep in mind
If you delete column A or B (your original name data field), you’ll break the sheet because the data in columns C and D is dynamic. To get rid of those columns, copy the columns you want to keep and paste into a new sheet using Excel’s “Paste As Values” option.
Finally, remember to save your sheet as a .csv before importing into your email blasting tool!