Clean Up Data for MailChimp Import

MailChimp’s sample import file isn’t a bad place to start with getting your contact into your audience. But it can also leave some messy data on your hands.

Here are some tips to clean up up your data for MailChimp in Google Sheets before you import as a csv:

1) Get your column names correct. Review the import template and change your column headings to match MailChimp’s names. If you’re using custom merge tags, set those up as well.

2) Clean up names with improper capitalization and first and last name in the same column. With a function you can separate the two names into the first and last names and normalize the case of the names as well. Breaking up these names now allows you to use them as separate merge tags in your emails.

Here’s the function to clip the first name from the full name in column A and use titlecase on the name:

=Proper(TRIM( LEFT( SUBSTITUTE( A2 , " " , REPT( " " , 100 ) ) , 100 ) ))

Here’s the function for pulling the last name from the full name from Column A to Column B:

=Proper(TRIM( RIGHT( SUBSTITUTE( A2 , " " , REPT( " " , 100 ) ) , 100 ) ))

Okay only 2 tips so far. What’s your favorite tip for bringing data into MailChimp cleaner or faster?