Creating single-space postcodes

Centroids and polygons

Outward and inward bound representation

The current specification represents the postcodes in a set format that defines the postcodes as having an inward and outward postcode ‘code’. Code-Point and Code-Point with polygon postcodes have 0, 1 or 2 spaces between the in and out code.

The table below identifies how postcodes are currently shown in the data.

Postcode structure	Number of spaces
AANNNAA	0 spaces (represented as AANNNAA) for example: PO143RW
ANN NAA	1 space (represented as ANN<>NAA) for example: PO14 3RW
AN NAA	2 spaces (represented as AN<><>NAA) for example: B1 5AP

Single-space postcodes

The Code-Point and Code-Point with polygons postcodes are currently represented as above; however, there may be a user requirement to represent each postcode in a uniformed single-space format.

The aim of this section is to offer some guidance on how to process the Code-Point and the related Code-Point with polygons data to generate postcodes with a single space.

The single-space instructions are applicable to both the postcode point and unit polygon products. Microsoft Excel, Microsoft Access, MapInfo, ESRI and QGIS GIS formats have been included to provide guidance when using comma-separated values (CSV) and other formats.

The underlying theory for all of the methods is principally the same, in that all current spaces are removed and then a single space added before the third character from the right.

The NTF format is not included in this chapter as it is not compatible to a single-space format.

These instructions apply to postcode units only, and not to vertical streets (which are only found in the Code-Point with polygons dataset). To ensure that the vertical street references are not corrupted, remove them from your data before applying these instructions.

CSV single-space postcodes using Microsoft Excel and Access

1. Open the CSV file with Excel so that the data is displayed in columns.

2. In a new blank column, click in the first cell of the new column (excluding the row column names).

3. In the function line, enter in the following function command where A1 is the column containing the postcode: =TRIM(LEFT(A1,LEN(A1)-3))&" "&RIGHT(A1,3)

This should now produce a column containing postcodes with a single space. For polygon data, extend this formula downwards, stopping when you reach any vertical street references; for example, those beginning with ‘V’.

The same method can be employed in Microsoft Access, using an update query rather than the function line. The functions listed above are the same for Access and Excel.

MID/MIF and TAB single-space postcodes using MapInfo

The process within MapInfo is the same regardless of whether the original supply is in TAB or MID/MIF, as both get imported to MapInfo and opened as a .TAB file.

1. Create a new column, of Character (8) type, in the Code-Point Polygons table (CP_Polys.TAB) as column 3. Give it a meaningful name like ‘SS_Postcode’ (go to Table > Maintenance > Table Structure). Save the table.

   

   Modify Table Structure: CP_Polys screen with SS_Postcode field highlighted.
2. Click on Query, Select – this brings up the Select Query dialogue box. Ensure that the Code-Point Polygons table, CP_Polys, is being selected from and enter the following expression in the that Satisfy field:

POSTCODE not like “V%”

1. Change the Store Results in Table field to read ‘UpdateThese1’. Click the OK button to apply the query.

   

   Select dialog box showing the relevant query parameters detailed above
2. The next step is to update the SS_Postcode attribute. Click Table and then the Update column tab. This opens the following dialogue box:

   

   Update Column dialog showing the table and column to update and value from dropdown filled with details in step 5.
3. Ensure that the Table to Update field has ‘UpdateThese1’ as its value. From the Column to Update drop-down menu, select the previously-added column; for example, ‘SS_Postcode’, making sure the Get Value from Table is the same table as in the Table to Update field.

4. Click the Assist button. This opens the following Expression dialog box:

   

   Expression dialog box without details entered

1. In the Type an expression box, key in the following function command:

RTrim$(Left$(<insert column name of original postcodes>,(Len<insert column name of original postcodes> (<insert column name of original postcodes>e)-3)))+" "+Right$(<insert column name of original postcodes>,3)

1. Click the OK button to apply the update.

This process must then be re-run for the postcodes that were originally vertical streets also:

1. Change the Store Results in Table field to read ‘UpdateThese2’. Click the OK button to apply the query.

POSTCODE like “V%”

• Click on Query > Select – this brings up the Select Query dialogue box. Ensure that the Code-Point Polygons table, CP_Polys, is being selected from and enter the following expression in the that Satisfy field:

  

  Select screen showing records being selected from the CP_Polys table
• The next step is to update the SS_Postcode attribute. Click Table and then the Update column tab. This opens the following dialogue box:

  

  Update column dialog box

  1. Ensure that the Table to Update field has ‘UpdateThese2’ as its value. From the Column to Update drop-down menu, select the previously-added column; for example, ‘SS_Postcode’, making sure the Get Value from Table is the same table as in the Table to Update field.

  2. Click the Assist button and in the Type an expression box, key in the following function command:

  3. RTrim$(Left$(<insert column name of original postcodes>,(Len(<insert column name of original postcodes>)-3)))+" "+Right$(<insert column name of original postcodes>,3)

  4. Click the OK button to apply the update.

If you now browse the Code-Point Polygons table you should see that the SS_Postcode column is fully populated with single-space postcodes for both ‘standard’ postcodes and vertical street postcodes. You can choose to delete the VS_Postcode column at this point, as the new SS_Postcode contains all the single- spaced postcodes for all features.

Shapefile single-space postcodes using ESRI ArcGIS

These steps assume that the data has already been imported into ArcGIS and that the user has the correct permissions to edit the Shapefile. It is advised to try this method on a copy of the original data, and not the only copy of the data, in case of error.

Once the Shapefile is open, the required new fields can be added.

1. Right-click on the layer in the Table of Contents (down the left-hand side of the map window) and open the Open Attribute Table.

The first step is to add a new column to hold the newly formatted postcode.

1. Click on the Options button then click on Add Field.

• This opens the following dialogue box:

  

  Add field dialog box

  1. Key in an appropriate name, for example, ‘NewPC’, in the Name box and change the Type box on the drop-down menu to ‘text’; also change the Field Properties Precision/Length to ‘8’.

  2. Click OK and the field is added.

  The next stage is to ensure that only postcode units are updated and not any vertical street references that might also be in the table.

  1. Click on Selection, Selection By Attributes.

  This brings up the Select By Attributes dialogue box:

  

  Select By Attributes window

1. Ensure that the correct Layer is being selected from, in this example, ‘L’.

1. Ensure that the Method field you have chosen is ‘Create a new selection’.

1. Then, enter the following expression in the query field at the bottom of the dialogue box:

“POSTCODE” NOT LIKE ‘V%’

1. Click the OK button to apply the query. This selects all the postcodes that are not vertical streets from the attribute table.

The final stage is to update the new column:

1. To populate the fields, the table must be made editable.

2. Return to the map window. Do not close the attribute table as it will be required later.

3. Click on the Editor drop-down selection =▼.

   

   An Untitled-ArcMap-ArcInfo screen with a red circle drawn around the Editor drop down button.

• If this toolbar is not already loaded, then right-click on an empty part of the grey area on the map window and all the available toolbars will be listed. Simply click on the Editor toolbar and it will be loaded to the toolbar.

1. Ensure that the correct Layer is being selected from, in this example, ‘L’.

1. Ensure that the Method field you have chosen is ‘Create a new selection’.

1. Then, enter the following expression in the query field at the bottom of the dialogue box:

   

   Field Calculator winow showing the fields FID, POSTCODE, UPP, PC_AREA, NewPC

1. In the bottom dialog box, enter the following function command:

RTrim(Left([<insert column name of original postcodes>],(Len([<insert column name of original postcodes>])-3)))+" "+Right([<insert column name of original postcodes>],3)

1. Ensure that the lower-left tick box Calculate selected records only is checked.

1. Click OK to update the column. This will update your new column with a single-space postcode.

1. Finally, go back to the map window, click on the Editor toolbar and select Stop editing. It will prompt to save the edits. Click Yes.

Shapefile single-space postcodes using QGIS

These steps assume that the data has already been imported into QGIS and that the user has the correct permissions to edit the Shapefile. It is advised to try this method on a copy of the original data, and not the only copy of the data, in case of error.

Once the Shapefile has been opened in QGIS it can be edited and a new field with the reformatted postcodes added.

1. Right-click on the layer you wish to edit in the Layers window and select Open Attribute Table.

A new column must be created to hold the newly formatted postcodes.

1. Select the Toggle Editing Mode button in the top left-hand corner of the attribute table. This will activate a range of other buttons in the top bar. Select the New Column button in the top right-hand corner of the attribute table.

   

   Attribute table AB table with the following columns: Postcode, UPP, PC_Area.

2. The following dialogue box will open, prompting you to set the name and parameters of the field. Ensure you set the field type as Text (String) and set the width to 8 characters long as shown below.

   

   Add column dialog

1. When the parameters have been set click OK. The extra field will be created and will contain null values. To populate this field, select the Open Field Calculator button in the top right-hand corner of the attribute table. The following dialogue box will be opened.

1. Click the checkbox Update existing field and then choose the field you have created from the drop-down underneath. In the Expression box copy and paste the following code: trim(concat( left( "<insert column name of original postcodes>" , length( "<insert column name of original postcodes>" )-3) ,' ', right( "<insert column name of original postcodes>" , 3) ) )

   See image below for example.

   

   Field calculator window showing the expression above in the expression window

2. After selecting OK, the dialogue box will close, and the new column will be updated with single-spaced postcodes.