Mailchimp – how to correct the name field before import

One of the most common questions I get from Mailchimp customers is how to correct the first name field for contacts. Often the first name field includes the last name or is in upper or lower case. It isn’t simple in Mailchimp to, for example, separate the first and last names in a single field once the data are in Mailchimp.

In this video tutorial I show how to:

• Remove extra and unwanted spaces in your contact list.
• Change the case of first names so that the first letter of the name is uppercase and then the rest of the name is lowercase.
• Separate first names and last names into separate fields. I show how to do this where you first and last names are space separated and comma separated.

Excel formulas to separate first and last names

The formulas I use in Excel, and demonstrate in the tutorial, are listed below.

How to separate names where a space is used as the separator

If your CSV or Excel file has first and last names separated by an empty space then here are the Excel formulas to use.

• To remove empty spaces before the name: =TRIM(B2)
• To change the case of the name to be uppercase first letter and lowercase for the rest of the name: =PROPER(B3)
• To only show the first name from the name field: =IF(ISNUMBER(SEARCH(" ",D2)),LEFT(D2,(FIND(" ",D2,1)-1)),D2)
• To only show the last name from the name field: =IF(ISNUMBER(SEARCH(" ",D2)),RIGHT(D2,LEN(D2)-FIND(" ",D2)),"")

How to separate names where a comma is used as the separator

If your CSV or Excel file has first and last names separated by a comma then here are the Excel formulas to use.

• To remove empty spaces before the name: =TRIM(B2)
• To change the case of the name to be uppercase first letter and lowercase for the rest of the name: =PROPER(B3)
• To only show the first name from the name field: =IF(ISNUMBER(SEARCH(",",D2)),LEFT(D2,(FIND(",",D2,1)-1)),D2)
• To only show the last name from the name field: =IF(ISNUMBER(SEARCH(",",D2)),RIGHT(D2,LEN(D2)-FIND(",",D2)),"")
• To remove empty spaces before the last name: =TRIM(E2)
• To remove empty spaces before the first name: =TRIM(F2)