Copyright Attribution
Excel® is part of the Microsoft® Office suite which is a registered trademark of Microsoft Corporation.
Homepage Research Staff Publications Useful Links Email Us 
Learning Materials Glossary Past Workshops 
Geographical Referencing Learning Resources 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 |
Click [here] to open the example spreadsheet. You will need to have Excel on your computer to view and modify the spreadsheet.
Excel® is part of the Microsoft® Office suite which is a registered trademark of Microsoft Corporation.