UK Postcode / Postal Codes (including RexEx formula)

Version 5

    Description:

    UK postcodes come in 2 parts, an outward code and an inward code.

    The following formulas deal with the splitting and concatenation of valid UK postcodes.

    It assumes that the UK postcode is valid and therefore separated by a blank space.


    If the 2 parts of the postcode aren't split by a blank space (or some are, some aren't as is commonly the case), we can now use the Tableau 9.0 REGEXP_EXTRACT function to extract just the 1st part (which is the accuracy Tableau currently generated Long/Lat for in the UK). Post Codes (especially some in London) can have slightly different make up, and I've tried to capture here every use-case, but if anyone finds some cases where this fails please add the extra rule to the below RegEx formula

     

     

    Example Calculation:

     

    //UK Outward Postcode

    LEFT([UK Postcode],FIND([UK Postcode]," ")-1)

     

    //UK Inward Postcode

    RIGHT([UK Postcode],3)


    //UK Postcode

    [UK Outward Postcode]+" "+[UK Inward Postcode]


    //UK Postcode (with Spaces or Non Space) and return 1st part

    REGEXP_EXTRACT( [UK Post Code Full],'(\w{2}\d{1}\w{1}|\w{1}\d{1}\w{1}|\w{1,2}\d{1,2})\s*\d{1}\w{1,2}' )


    //UK Postcode (if you can't use RegEx - some live connections won't support this) with no spaces

    TRIM(LEFT([UK Postcode],LEN([Postcode])-3))

     

     

     

     

     


    Comments:

     

     

     

     

    Related Functions:

     

     

     

    Further Reading/Examples: