How to extract postcode from address list in Excel?

When managing customer information in Excel, it’s common to have a list of full addresses where each entry contains both street details and a postcode (zip code). If you need to isolate and extract just the postcodes from these addresses—perhaps for mailing lists, regional analysis, or data formatting—manually separating them one by one can be tedious and error-prone, especially with large datasets. Fortunately, Excel offers practical ways to quickly and efficiently extract multiple postcodes at once without manual effort. This article provides step-by-step instructions for extracting postcodes from addresses using an Excel formula and a VBA macro solution.

Extract postcode with formula in Excel

Extract postcode with User Defined Function in Excel

Extract postcode with formula in Excel

In many common address lists, the postcode is located at the end of the address string and is not longer than 8 characters. This method is most suitable when all addresses in your list follow a consistent format and the postcode appears as the last element of each address, separated by spaces.

To quickly extract the postcode using a formula, follow these steps:

1. Select a blank cell where you want the postcode to appear (for example, B1 if your address is in A1). Enter the following formula:

=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))

2. Press the Enter key. The postcode from the address in A1 will be displayed in the selected cell.

3. To apply this formula to other addresses, select the cell with the formula, drag the fill handle down along the column to cover all address rows, and Excel will automatically extract the postcode for each address.

Tip: This formula assumes that the postcode is the last item after the final space in the address string. If your addresses are structured differently, such as postcodes appearing in the middle, or separated by commas, this formula may not return the correct result. To extend this method for international postal codes or custom formats, you may need a different or more advanced formula approach, or try the VBA solution below.

Extract postcode with User Defined Function in Excel

If your address data is more complex, inconsistent, or contains postcodes in various formats and positions within the string as following screenshot shown, a VBA macro can provide more flexible extraction options. This automated method quickly processes an entire column of addresses to isolate and return only the postcodes, saving significant time and minimizing manual effort.

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.

2. In the VBA window, click Insert > Module to create a new module. Copy and paste the following VBA code into the module window:

Public Function ExtractPostcode(text As String) As String
    Dim reg As New RegExp
    Dim m As MatchCollection
    reg.Pattern = "\b([A-Z]{1,2}\d{1,2}[A-Z]?\s*\d[A-Z]{2}|\d{5}(?:-\d{4})?|\d{6})\b"
    reg.IgnoreCase = True
    reg.Global = False
    
    If reg.Test(text) Then
        Set m = reg.Execute(text)
        ExtractPostcode = m(0).Value
    Else
        ExtractPostcode = ""
    End If
End Function

3. After pasting the code, In the vba editor windown, choose Tools > References. See screenshot:

4. In the References dialog box, check Microsoft VBScript Regular Expressions 5.5 and click OK.

5. Return to the worksheet, and enter this formula: =ExtractPostcode(A2), drag the fill-handle down to other cells. All postcodes are displayed at once, see screenshot:

Tip: With this code, you can automatically extract postal codes from any country or region in Excel in just seconds; by simply tweaking the regular expression to match the postal-code rules of your target area, you can quickly adapt to different formats—such as the UK’s “SW1A 1AA,” the US’s “12345-6789,” or China’s “100000”—greatly boosting your data-cleaning and analysis efficiency.

Relative Articles:

• Extract initials from names in Excel
• Extract decimal value from string in Excel