Function Problems

Extract a Postcode from an Address with MID and FIND in Excel

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Extract a four-digit postcode from a text address using Excel formulas.

Extract a Postcode from an Address with MID and FIND in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The address is stored in one cell as street, suburb, postcode, and state, separated by commas.
Observed behavior
The desired result is to return 2007 from the example address “310 Wattle St, Ultimo, 2007, NSW.”
Before you start

Place the complete address in cell A1 and check that a comma and a space appear before the postcode. The MID formula below assumes the postcode is four characters long and follows the second comma.

Solution 1Recommended

Extract the four-digit postcode with MID and FIND

Use this formula when the postcode follows the second comma and a space, as in the example address.

1
Enter the address

Type 310 Wattle St, Ultimo, 2007, NSW into cell A1.

2
Enter the formula

Select an empty cell, enter =MID(A1,FIND(", ",A1,FIND(", ",A1)+2)+2,4), and press Enter.

3
Apply it to other rows

Drag the formula cell’s fill handle down to extract postcodes from other addresses with the same layout.

Extract the four-digit postcode with MID and FIND
Check the address format: The formula finds the second comma followed by a space and returns the next four characters. Different spacing, field order, or postcode lengths require an adjusted formula.
Extract postcodes in WPS Office

Extract address postcodes in WPS Spreadsheets

Use MID and FIND in WPS Spreadsheets to extract a four-digit postcode from the same comma-separated address layout.

  1. 1. Open your spreadsheet: Open WPS Spreadsheets and select the workbook containing your addresses.
  2. 2. Check the address cell: Confirm that cell A1 contains an address with the four-digit postcode after the second comma and a space.
  3. 3. Enter the formula: Select an empty cell, enter =MID(A1,FIND(", ",A1,FIND(", ",A1)+2)+2,4), and press Enter.
  4. 4. Extract other postcodes: Drag the formula cell’s fill handle down to apply it to similarly formatted addresses.
Extract postcodes with familiar spreadsheet formulasOpen and edit Microsoft Excel XLSX filesFill a formula down across multiple address rowsUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula search for the second comma?

In the example, the first comma separates the street from the suburb, and the second comma appears immediately before the postcode. Finding the first comma alone would start the extraction at the suburb.

What if my postcode is not four digits long?

In a supported Excel version, use =TRIM(INDEX(TEXTSPLIT(A1,","),1,3)) to return the entire third comma-separated field, regardless of its length.

Why does the MID formula return an error or the wrong text?

Check A1 for two comma-and-space separators before the postcode. The formula assumes the postcode begins immediately after the second separator and is four characters long.

Will the formula preserve a leading zero in a postcode?

Yes. MID returns text, so a four-character postcode such as 0200 retains its leading zero. Avoid converting the result to a number if that zero is significant.