About this Resource
    Resource Category:
  • Concepts
  • Methods
  • Datasets
  • Examples

Geo-Refer logo Geographical Referencing Learning Resources

Correctly Formatting Postcodes with Microsoft Excel

The attached example shows how a series of Excel functions can be used to clean up a variety of common postcode errors. It does not contain a full postcode syntax checker and does not correct all the input postcodes, although it places them into valid formats. This example can be used in order to develop more sophisticated postcode checkers or to reformat users' own data. It is important to remember that prior to the release of Microsoft Office 2007, Excel worksheets are limited to 65,536 rows and many postcoded datasets may exceed this limit.

Here is a brief overview of the functions used in the example file, which are particularly useful when processing postcode strings in Excel. The example file demonstrates these functions used in various combinations.

Excel function Description
=UPPER(A2) Returns the contents of cell A2 converted to upper case. Numbers and spaces within the text are unaffected, hence "so17 1bj" is converted to "SO17 1BJ"
=MID(A2,4,3) Returns a subset of the text in cell A2, of length 3 starting from position 4, hence "so17 1bj" is converted to "1bj"
=LEN(A2) Returns the length of the text in cell A2, expressed as the number of characters, including spaces, hence "so17 1bj" returns 8
=IF(H4="I","1",H4) Tests the condition that cell H4 contains the character "I". If the test is true, it is replaced with the character "1", otherwise the original contents of the cell are returned.
=CONCATENATE(A2,B2,C2," ",D2) Takes the text contents of cells A2, B2, C2 and D2 and returns them joined together as a single text string with a space between the contents of C2 and D2

Example File

Click [here] to open the example spreadsheet. You will need to have Excel on your computer to view and modify the spreadsheet.

Copyright Attribution

Excel® is part of the Microsoft® Office suite which is a registered trademark of Microsoft Corporation.