The address field in your Mailchimp list is designed to store a contact’s entire physical address, including the zip code and country. Address field data is required for certain features, like postcards or address-based segmentation. If your contact file has separate columns for city, state, country, and other details, we recommend you combine them before you import.
In this article, you’ll learn how to combine address fields before you import your contacts.
Before You Start
- To work with certain features in Mailchimp, physical address data must include the country. You may need to add this information to your spreadsheet before you combine address list fields.
About the Formula
When you prepare your file for import, you can use a spreadsheet formula to quickly concatenate, or combine, your contacts' address data into one column.
If you use Microsoft Excel or Apple Numbers, you’ll start your formula with the CONCATENATE function. Then, you’ll add the column letter and row number of the cells you want to combine, separated by commas and quotation marks to create two spaces between each part of the address.
For example, let’s say your address data spans columns D through I, and that your list begins on row 2, under the header. Your formula should look something like this.
=CONCATENATE(D2,” “,E2,” “,F2,” “,G2,” “,H2,” “,I2)
In Google Sheets, your formula will include the ARRAYFORMULA function.
=ARRAYFORMULA(CONCATENATE(D2,” “,E2,” “,F2,” “,G2,” “,H2,” “,I2))
Notice that in both formulas, there are two spaces between each pair of quotation marks. This spacing helps Mailchimp accurately recognize the parts of your address when you import. There should be no other spaces or in the formula.
Now we’ll show you how to apply your formula to your spreadsheet.
Combine Address Fields
To use the spreadsheet formula to combine multiple address fields into one, follow these steps.
- Add two empty columns to your spreadsheet.
- In the first cell of the first empty column, enter your concatenation formula to combine your address fields.
- Click the corner of the cell you just worked with, and drag the fill handle over the other cells in the column.
Click the corner of the cell you just worked with, and drag the fill handle over the other cells in the column.
When a row includes empty cells, you may see extra spaces between the parts of the address. These addresses should still import correctly, but you’ll want to check the contact’s profile to confirm.
- Right-click the formula column and choose Copy.
- In the second column you created, right-click to choose Paste special, then choose Paste values only.
The formula in the first column combines the address data. The second column duplicates the outputs as values that you can use in Mailchimp.
When you upload your list to Mailchimp, you'll have the option to skip unnecessary columns, so you won't have duplicate individual and combined address fields.
After you’ve combined the address fields in your spreadsheet, double check your file proper formatting. Then, save your file as a CSV to import it into Mailchimp.
To confirm an address was imported correctly, navigate to the contact’s profile page. Then, find the address data and click Edit. Review the modal to make sure the right data is in the right fields.