I’ve worked with several artists who accumulate the names, emails, and mailing addresses of people who like their work. But then they don’t do anything with the info because it is scattered on sheets of paper, business cards, etc.

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 the mailing.

I asked her what column headers she was going to use 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

• I was glad to see First and Last in separate columns.
• Address line 1 would be for the street address or PO Box.
• My issue is with Address line 2, which represents the line including city/state/zip

This format doesn’t work for transferal into my client’s Address Book, which requires the separation of city, state, and zip. Here are the columns I’ll use:
First Name, Last Name, Address, City, State, Zip, Email, Phone

• city/state/zip go in different columns.

I always do the initial data entry of a project like this in Excel. The data is easy to manipulate and it’s easy to check for duplicates.

Once all the contact info is entered, I’ll make a copy of the file for the upcoming mailing. I’ll remove phone and email columns and then create a column to combine city/state/zip (see column F in the screen shot below). I’ll enter the following formula into this column of cells:

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

How does this work? I entered the information for city, state, and zip into 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 2″ [I took out Address line 1 so the screen shot would be smaller.]

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

I’m including the breakdown because the more “Excel speak” you know, the more you can command the program to do what you want it to do!