Working with leading zeros in Northeast zip codes

Overview

If you have a national constituent base or are located in the Northeast, you can't escape those leading zeros on zip codes. However, Excel and other spreadsheet programs often remove them. In this article, we provide a couple pointers for keeping your zip codes intact and accurate before importing them into LGL.

Your Excel file contains a column of zip codes that import without leading zeros

Your column of zip codes appears to have leading zeros (for example, "08904"), but the values stored in the cell are actually lacking the leading zero (they look like this: "8904"). You can see this by clicking into a cell and seeing what the actual text is.

If you try to import this Excel file into LGL, your zip codes will accurately reflect the value Excel contains in the cell: or "8904". This will create an incorrect zip code in the imported data.

Solution

To format your zip code column to display leading zeros in Excel, click Format > Cells, choose “Custom”, and then type a string of five zeros in the “Type:” field, as shown here:
When you have your Excel file looking perfect (displaying a leading zero in applicable zip codes), in Excel click File > Save As and save the file as a *.csv formatted file. (One of the reasons we strongly encourage using the CSV format is that the file type shows you the actual data that will import.)
Check the CSV file by opening it with a plain text tool (don't double-click it) such as Notepad on a PC or TextEdit on a Mac. Do this to confirm you are seeing the leading zeros for addresses that have them. Close the file without updating/changing it.
Import the CSV file with the appropriate mapping. Before accepting the import, check a few records in the preview (click the -Full Record- option) to be sure you are seeing the leading zeros.

NOTE: If leading zeros are not present in your Excel file, you can apply the above instructions once you have corrected the zip codes to display properly.

Your data contains a large number of incorrect zip codes (they should have leading zeros but don't)

Say a large number of addresses that lack the leading zero were imported into your account, resulting in many bad addresses.

Option 1: Turn on Address Verification in your LGL account and let the computer do the work for you.

NOTE: You can enable the Address Verification feature and it will work through your account to verify addresses. This will take about 24 hours to complete. Any address with a Northeast zip code missing the leading zero should be corrected once the address is verified.
Option 2: If you are able to undo the import that created those addresses (click into the import and then click the Undo Import button to accomplish this), you could remove the data that way, fix the columns per the instructions above, and then re-import the zip codes.
Option 3: As a last resort, you can export the relevant addresses and re-import them after correcting the zip codes as described above.

Exporting and importing addresses

a. In the Constituents tab, perform an advanced search and search on the State/States field.

b. In the criteria fields, type the states that have leading zeros separated by commas. Do not add spaces after the commas. Your state/states search criteria should look something like this: "ME,NH,VT,MA,RI,CT,NJ"

Note: This presumes you have uniformity in your state abbreviations/text fields. If you don't, you'll have to use search terms that match your values. Also note that PR (Puerto Rico) and VI (Virgin Islands) have leading zeros in their zip codes.

a. When you have the results you expect, click the Export Results button in the upper right of your screen.

b. Use a custom export, and choose the following fields:

From Constituent Fields, select LGL Constituent ID
From Contact Info. Fields, select Preferred Address
Customize your Preferred Address field (by clicking Customize next to the field's name), and then check the "Split" option under Street, being sure to include the LGL Address ID).

The screen shot below shows what this should look like in your account.

Move to the Save Export step, and make sure the options for "spreadsheet friendly" and "inactive info" are not selected. Then download your report and open it in Excel or the equivalent. Format the zip codes so they have the leading zero. Then follow the instructions above for formatting your Excel column to display leading zeros.
When you have your CSV file, import it back into LGL making sure that your LGL Constituent ID, LGL Address ID, and other mappings are correct.