Overview

In this guide we will discuss how to export addresses to Excel for use with Mail Merge.

Required Permissions

Accessing the Tool

Exporting Interactive Reports to Excel

Interactive Reports

1. In CMA on the left menu click on Interactive Report Icon.

2. Select Membership > Click on the + sign to open the report options > select Member Address Labels – Avery.

3. Set all the criteria’s to filter what members you want included in the export:

   A few important pieces of criteria to consider:

   1. Member Types –multi select the member types you want included

   2. Field – how many address lines to include.

   3. Notification Types – defined by the club: what type of events and communication does the club send out.

   4. Sort By - sort by name will sort Alpha Last Name. There are numerous other sort options, member number, zip code, etc.…

   5. Display Name – this depends on how many name fields you use in the member file. Default will
      print the first, middle, last name of the member. The statement name is usually used as a more
      formal name such as Mr. John Smith & Mrs. Tina Smith.

4. Once all the criteria are set, then click View Report.

5. Click on the Export Icon > select Excel > Give it a File Name and save to the directory you want the file stored.

Formatting Data in Excel

1. Open the File in Excel > top tool bar click Data.

2. Notice Column A and C are not showing: expand both columns.

3. Highlight column B > Cut and Paste to Column A.

4. Highlight column A > Top toolbar Select Data > Text to Column.

5. Check Delimited > Next.

6. In the this screen check Tab and Other > click in the blank box next to Other> 
   hold the ALT Key down while entering 010 from the key pad (this will not populate the box with the 010 this is a command and the box will remain blank) *very important; this will not work using the numbers at the top of thekeyboard, you must use the numbers on the keypad only

7. You will notice in the Data preview box the address lines have moved over to columns > click Finish.

8. A box will appear > click OK

   1. The file at this point will still need some data manipulation before it is usable. Some members might have three address lines and some might have more.

   2. Before continuing scroll down and find the columns with the longest address and note the City/State/Zip Code column (city, state and zip are in the same cell at this point).

9. Highlight the entire spreadsheet by clicking on the Icon to the left of Column A.

10. Right click the mouse anywhere on the highlighted spreadsheet > Format Cells > click Alignment Tab > 

11. A few things to look for:

    1. Un-check Wrap text

    2. Un-check Merge cells

    3. Text direction and Vertical can be your preference

12. Click OK.

13. The spreadsheet will still be highlighted and you should still be in Data > Adjust your column width and height >

14. click on Sort > Select the last Column containing city/state/zip code > sort on Values > select Order Z to A.

15. Find the first line that the city and zip are not aligned to the above address lines > highlight all lines.

16. containing the city/state/zip > right click Cut and Paste to the same columns as the City column above.

17. If a column contains a Country > Highlight the column Cut and Paste to 3 columns over *if you do not do this step the following steps will overwrite the country if not moved.

18. Top Tool Bar click on the Data > Highlight the column containing the city/State/Zip Code > Select Text to Column.

19. Check Delimited > Next.

20. Un-check everything and check Comma only > Finish (this will move the City to a separate column).

21. Highlight the column containing the State/Zip Code you should still be on the Data tab > Select Text to Column.

22. Check Delimited > Next

23. Un-check everything and check Space only > Finish (this will move the Zip Code to a separate column).

24. Delete the blank column > Shift Cells Left.

25. Highlight the entire spreadsheet > click Sort > Select the Column containing the State > sort on Values > select Order A to Z (this will help find addresses in other countries or not entered correctly).

26. Once City, State, and Zip Code columns are aligned, highlight the Zip Code Column > right click on the highlighted column > Format Cells > select the Number Tab > select Special > Zip Code (do not select Zip Code + 4).

27. Top Tool Bar click Home and highlight Zip Code column or All columns > click Align Text Left 

Best Practices

FAQs