Excel has an undesirable habit of stripping leading zeros from many Postal Codes when opening spreadsheets. This automated change is often overlooked and the altered data gets loaded into your system of record. If your data source ends up with this problem, use the following logic to normalize this data for US postal codes:
- Create a new task in your job, and use the infer task template
- Specify a filter that limits records where zip code length is between (4 / 4) and country matches (United States)
- For “What inference mapping would you like to use?”, specify “Reference – US Zip Codes”
- For “Using the match method of”, specify “Ends with: Reference value ends with input value”
- Under Advanced configurations, add a filter where “zip code begins with (0)”
- If needed, create another task using similar logic for Zip+4 postal codes.
This method can also be used with minor modifications for the following countries that also have postal codes with leading zeros.
- Germany (one leading zero in places)
- Spain (one leading zero in places)
- Finland (one or 2 leading zeroes, plus special case for one Helsinki postal code with 4 leading zeroes (00002))
- France (one leading zero in places)
- Italy (one, 2 or 3 leading zeroes in places)
- Norway (one leading zero in places)