Mailing lists can play a huge role in a business’s marketing plan. I’ve worked with several artists who accumulate the names, emails, and mailing addresses of people who liek their work but then don’t do anything with the info because it isn’t digitzed.

Recently, I was asked to digitize an artist’s mailing list which would be immediately used to promote an upcoming show. The artist’s sister had offered to do a mailing to his list.

I asked her what column headers she was using for the mail merge. She sent me an Excel spreadsheet with these column headers:
First, Last, Address line 1, Address line 2, Address line 3
This would mean that city/state/zip go in the same column, i.e. Address line 2.

This format doesn’t work for future transferral into my client’s Address Book, so I’ll stick with my plan of separating each piece of information:
First Name, Last Name, Address, City, State, Zip
Note that first and last name go in different columns, city/state/zip go in different columns.

I always do a project like this in Excel. The data is easy to manipulate and it is easy to check for duplicates.

So I’ll enter all the data with my client’s Address Book in mind. Then for the mailing that his sister is going to do, I’ll remove phone and email columns and then create a column to combine city/state/zip. I’ll enter the following formula into this column of cells:

=C2&”, “&D2&” “&E2

How does this work? I entered the information into each of the individual cells, i.e. in columns C, D, & E.

Then column F combines them for the format requested for the mailing, i.e. “Address line 1″ [this would actually be Line 2, and the street address would be Line 1, however, adding this column would have made the screenshot harder to read.]

Let’s break down the formula in cell G2: =D2&”, “&E2&” “&F2
= tells Excel that commands are coming up
D2 tells Excel to insert the contents of column D, row 2, i.e. the city
& tells Excel to join data from different cells
“, “ tells Excel to include a comma and then a space
&E2& tells Excel to insert data from column E, row 2, i.e. the state
” “ tells Excel to insert a space (I entered “, space bar, “)
&F2 tells Excel to insert data from colum F, row 2