Have you found it increasingly more difficult to distribute your accounts by region? Perhaps you’ve found a number of leads that were created in the mythical nation of Aremica? Whether you might be looking to enable Salesforce’s State and Country Picklists feature, working to better locate businesses for your field sales representatives, or preparing for the Spring ’15 Maps feature, there are plenty of good reasons for cleaning address data in Salesforce. Follow the steps below and you’ll be well on your way to a cleaner Salesforce instance.

The typical Salesforce address field, whether used for billing or shipping, is a compound of five visible fields, Street Address, City, State/Province, Zip/Postal Code, and Country. The most readily cleansed of these is the Country field, with only about 200 possibilities, so consider attacking that field first. Export all of your records that need cleaning, ensuring that you retrieve the relevant address fields, and then you are ready to begin.

1. Cleaning Up Countries

To clean up your country fields, secure a copy of the ISO-3166 standard, which Salesforce uses for its picklist options. Open that file in Excel, then do the same with your exported records using a new tab or window. To the side of your country columns, say, BillingCountry and ShippingCountry, make a new column and fill it with a MATCH() formula that compares the country field to the picklist options. Filter out any records that find matches, as those don’t need to be touched in this step. Copy over the remnants to a new tab, then use an INDEX/MATCH formula to retrieve the Country Name for any records that have a valid country code specified (thus, a lead with ‘GB’ as its country can be quickly changed into ‘United Kingdom’). Finally, to fix the records that still don’t have a confirmed valid country, sort or filter the country column and copy ‘Canada’ from the picklist tab to replace such entries as ‘Cadana’, ‘Canda’, and ‘CANADA’. If you can’t determine what the country name was intended to be, don’t feel bad about leaving it blank – Salesforce will use your organization’s default country.

2. Cleaning Up States/Provinces

The next step after retrieving countries is to go after your provinces, states, and territories. To accommodate these, Salesforce has implemented ISO-3166-2 in its State Picklists feature. Follow similar steps to the above, but ignore the INDEX/MATCH formula. (That is, unless one of your reps decided to use AU-QLD instead of Queensland.) The process of finding correct states in the end step can be more challenging considering the thousands of possibilities, but filtering the possible states/territories by country will help significantly. As an alternative to taking on the task yourself, consider flagging the records for your reps to double check, as those in the right regions will know how to correct them.
Keep It Clean

At this point, you should be ready to activate state and country picklists, keeping your reps from entering any more invalid countries and states. Follow the Salesforce guide and you should be up and running quickly. Note that if at any point you want to deactivate the picklists, you’ll find that doing so is both quick and undamaging.

3. Other Tips and Tricks

The process for cleaning up your records’ Street Address, City, and Zip/Postal Code is different. While any zip code can be validated (either in a validation rule or Excel) based on country and length, to verify the truth of the data, you’ll be needing an address validation tool. If you’re a developer in the United States, look into the USPS’s Address Information APIs. In the United Kingdom, look into the Royal Mail’s Postcode Address File. If not, consider taking a trial of Capture+ from PostcodeAnywhere (capable of verifying US, UK, and international addresses) or AddressTools (capable of verifying state and country fields for countries worldwide). Substantially reducing the cost of lost marketing materials and returned bills caused by invalid and duplicate addresses will provide you with a speedy return on investment.