Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: post code validation

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi, in a cell, people would enter their post code, but it must be in correct pst code format XXXX XXX how can i get it to display an error message if the format is incorrect?



    BoB

    [ This Message was edited by: canablitz on 2002-04-01 07:28 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    are you sure they're static formats ?

    mine is XX8X 8XX (South West London)
    and my parents' is X88 8XX (Sheffield)
    friend's is XX88 8XX (Bristol)
    also X8 8XX (North London)


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    From :

    http://www.ccg.leeds.ac.uk/linda/geo..._defsfull.html

    What are the issues involved in postcode geography?

    The UK Postcode is a summary of an address in a form which can be read by a computer and thus enables mail to be sorted automatically. It consists of a group of letters and numbers whose format conforms to a set of standards. Every address in the UK to which the Royal Mail delivers has been given a postcode. Most postcodes represent a small group of households and therefore subdivide the UK into small areas.

    The Postcode geography, like the Census geography, consists of a group of nested spatial units, which are uniquely labelled using a hierarchical code. There are four levels in this hierarchy:


    Postcode Areas,
    Postcode Districts,
    Postcode Sectors, and
    Unit Postcodes.
    broken down into:
    Level in spatial hierarchy Number of units
    Postcode Areas 120
    Postcode Districts 2,679
    Postcode Sectors 8,820
    Residential Unit Postcodes 1,397,754
    Non Residential Unit Postcodes 151,765
    Large Users Unit Postcodes 171,541
    Total Unit Postcodes 1,721,060
    Residential delivery points 23,845,162


    The Post Office works by sending mail to District sorting offices from where it is redistributed to individual delivery points. The advantage of sorting mail at such a local level is that the local knowledge of the area can be used to correct mistakes. This is one of the strengths of the postcode system.

    This two stage process is represented in the Postcode itself, in that it has two parts separated by a space. The first part is called the Outward Code and contains information that allows the mail to reach the district sorting office. The second part is called the Inward Code and is used to target the mail to a small number of delivery points.


    The Postcode
    The Outward Code: The largest postal unit is the Postcode Area. Most of these are (or were) centred on major nodes in the national transport network. They are generally denoted by two alphabetic characters, chosen wherever possible to be a mnemonic for the place (e.g. OX is the Area code for Oxfordshire).

    Each Postcode Area is subdivided into Postcode Districts each denoted by a number ranging between 0 and 99; thus OX5 is a postcode district covering the Cherwell area in Oxfordshire. The Inward Code: The postcode sector is also indicated by a number; a single digit between 1 and 9 and then 0. Hence, OX5 2 is the Postcode Sector, which includes the village of Islip, in the Cherwell District. The full postcode is produced by adding two final alphabetic characters; OX5 2SH is a group of 48 households in Islip.


    Types of Postcode
    There are three types of Unit Postcode. Organisations which receive more than 25 items of mail each day in urban areas and more than 50 items in rural areas are normally given their own Large User Postcode. PO Boxes are also catagorised as Large User Postcodes.


    The Central Postcode Directory
    The Central Postcode Directory is a computerised directory that links each postcode in the UK to its ward, district, county and National Grid reference. The CPD was created by the Census Offices in conjunction with the Royal Mail. The first address within the postcode is used in making the linkage and all other address within the postcode are allocated the same ward, district and county. The directory is updated by the Post Office twice a year and is estimated to omit only about 0.1 per cent of postcodes. Unfortunately, the version purchased by the academic community (held by both the ESRC Data Archive, Essex, and at the MCC at MIDAS) is updated less frequently. This file is central to the Leeds GAS as it allows the postcodes that are entered to be linked to the census information.


    Postcode Formats
    The format of the Postcode is not truly common across the whole of Britain. The following Table lists the variety of different formats that exist.


    Format Example Number of Outcodes in the form
    AN NAA S3 9JD 70
    ANN NAA S34 3AB 262
    AAN NAA OX5 2SH 1052
    AANN NAA DN169AA 1482
    ANA NAA W1P 1PA 9 (only in W1)
    AANA NAA EC1A 1HQ 49 (only London districts EC1-4, SW1, WC1-2)
    AAA NAA GIR 0AA One-off postcode used by the National Giro Bank


    Other conventions are that the letter J is not used in either the first two alphabetic positions of the postcode and Q, V and X are not used in the first alphabetic postion. The letters I and Z are not used in the second alphabetic position. There are also restrictions on the letters used in the inward code which means that there are 400 letter pairs available.

    Hope this helps
    Chris

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow!
    That was a reply!
    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •