If you work with lists of Postcodes or addresses for mailing or analytical reasons, you have more than likely been frustrated by the inconsistent ways people type Postcodes into website forms or spreadsheets.
Upper case, lower case, mixed case. Spaces or no spaces. There are a seemingly infinite number of ways people can write a Postcode which doesn't help if you need to analyse customer information using the Postcode or just want to have a professional clean-looking address on the mailing you send out.
There are many systems available which will help capture a clean address or clean up your list of addresses for a fee but sometimes you just need to clean up a list of Postcodes quickly, easily and for free!
Postcode Formatting Excel Macro
As we work with Postcodes a lot, we created a simple macro in Excel to take a list of Postcodes and put them into a nice clean format. And we are offering this as a free download to anyone who can make use of this simple tool.
This spread sheet formats your Postcode list into a variable length e-GIF (e-Government Interoperability Framework) version of the Postcode which allows for a single space between the outward and inward parts of the Postcode.
For example, OX7 5LJ with a single space between the 7 and the 5.
In addition to ensuring your Postcodes are all upper case and fixing any spacing issues for you, the macro also fixes common mistype problems including:
- 0 instead of O (and vice versa)
I instead of 1
2 instead of Z
5 instead of S
- Shifted numbers such as " instead of 2
And so on.
It won't fix every conceivable problem but it does fix the most common issues quickly and easily. Of course, if you think there are issues we have missed just let us know and we will update it.
Download the Postcode Formatter User Guide >
To use this spread sheet you will need:
1. Microsoft Office including Excel
2. The ability to run macros in your version of Excel