UK postcodes contain:

  • An outward code (e.g. EC1A)
  • An inward code (9HF). 
  • A space separating the two. 

E.g. EC1A 9HF

You might wish to separate the two into two columns to, for instance, enable you to group results by just the outward code. 

Here's how to do it:

How to extract the first part (the outward code)

Postcodes with a space

If your postcodes are well-formatted and include a space:

  1. Click new column.
  2. In the enter your formula section, type the following (substituting "postcode-column" for your postcode column's actual name): postcode-column.extract("(.*) ")
  3. Name your column.
  4. Click done

All done! 🚀

(Note: using Amazon Redshift? This method won't work. Chat with us instead and we'll show you an alternative method 😃).

Postcodes without a space

If your postcodes aren't well-formatted and do not include a space:

  1. Click new column.
  2. In the enter your formula section, type the following (substituting "postcode-column" for your postcode column's actual name): postcode-column.length() - 3
  3. Name your column (e.g. length).
  4. Click Done. A new column will be added that contains the number of characters in each postcode minus 3.
  5. Click new column again.
  6. In the enter your formula section, type the following (substituting "postcode-column" for your postcode column's actual name and "length-column" for the new column you created in step 4 above): postcode-column.substring(length-column)
  7. Click done.

All done! 🚀

Postcodes where some have a space and some don't

  1. Click new column.
  2. In the enter your formula section, type the following (substituting "postcode-column" for your postcode column's actual name): postcode-column.replace(" ","")
  3. Name your column (e.g. Postcodes No Spaces)
  4. Click done.  
  5. Follow the postcodes without a space method above.

---------

Having trouble?

We're on standby to help! To chat with us, click the blue Intercom button in the bottom right of Trevor.io.

Did this answer your question?