• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Excel Formula to Extract Postcode from an Address

Excel Formula to Extract Postcode from an Address

I was asked recently in class how to extract a postcode from an address in the UK. The person asking needed a formula because the spreadsheet updates often and they wanted an automated solution.

The problem with extracting UK postcodes is that they are highly irregular. They will be at the end of the full address and can come in a different number of characters e.g. E1 6AX, RM3 8HN and LE41 8JX.

They are not as structured as a US zip code may be and harder to extract. Because of this the formula is intense, but I am going to break it down and explain it in detail.

Extract postcode from an address with an Excel formula

Extract Postcode from Address using an Excel Formula

The formula below is the finished article. If you are not used to writing formulas like this it may seem overwhelming, but we are going to look at it one piece at a time.

To use this formula, simply copy and paste and change the cell references to where your addresses are entered. If you want to know more about how this works, read on.

=RIGHT(SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))

How it Works

The RIGHT function has been used to extract the postcode. This function extracts text from the end of a cell. We can be sure that the postcode is at the end, so this function works for us here.

The RIGHT function requires two pieces of information from us. What text to extract the postcode from, and how many characters in the postcode.

=RIGHT(the text to extract from,how many characters)

What we do not know is how many characters are in the postcode of each address. We need Excel to calculate this.

To do so, we will find and mark the start of the postcode in the address. This unique mark can then be used to calculate how many characters in the postcode.

Finding the Start of the Postcode

The postcode always starts after the penultimate space of the address. It does not matter how many spaces are in the address, we can be sure that the postcode begins after the second from last space.

The formula below calculates what number space the penultimate space is. So it basically returns the position of the space.

LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)

The LEN function returns the number of characters in a cell. The SUBSTITUTE function finds and replaces each space with nothing, essentially removing them.

Altogether, by subtracting the number of characters in a cell without spaces from how many characters including spaces, gives us how many spaces there are.

The -1 is then used to return the occurrence of the second from last space.

This part of the formula actually occurs twice n the full formula as shown below.

=RIGHT(SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))

Uniquely Marking the Postcode in the Address

Now that we have found the postcode we want to insert a unique marker so that Excel knows where it is.

In the formula below the SUBSTITUTE function has been added to the previous formula to add an * at the start of the postcode (the second from last space).

SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)

This also occurs twice in the formula.

=RIGHT(SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)))

Calculate the Number of Characters in the Postcode

In the second part of the RIGHT function the formula below has been used to find the position of the * (our unique marker), and subtract that number from the total number of characters in the cell (LEN function). This returns how many characters are in the postcode.

LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))

I do hope this makes sense. It is a complicated formula if you are a beginner.

If you are interested in seriously improving your Excel formula skills. Check out our online course – Excel Formulas Made Easy – Learn over 100 Formulas.

More Awesome Tutorials

  • Count How Many Words are in a Cell
  • How to Use the TEXT Function in Excel
  • Prevent Formulas from Showing in the Formula Bar
  • Count How Many Fridays Between Two Dates

Related Posts:

  • Create a Case Sensitive Lookup Formula in Excel
    Create a Case Sensitive Lookup Formula in Excel
  • What You Need to Know About Dynamic Array Formulas in Excel
    What You Need to Know About Dynamic Array Formulas in Excel
  • Multiple Condition Lookup Formula in Excel
    Multiple Condition Lookup Formula in Excel
  • Advanced Excel Skills
    Advanced Excel Skills

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • Conditional Formatting Multiple Columns – 3 Examples
  • IF Function in Power Query Including Nested IFS

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·