Extract a Postcode from an Address with MID and FIND in Excel
Question details
Extract a four-digit postcode from a text address using Excel formulas.

- 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.”
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.
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.
Type 310 Wattle St, Ultimo, 2007, NSW into cell A1.
Select an empty cell, enter =MID(A1,FIND(", ",A1,FIND(", ",A1)+2)+2,4), and press Enter.
Drag the formula cell’s fill handle down to extract postcodes from other addresses with the same layout.

Extract the postcode field with TEXTSPLIT
Use this option in an Excel version that supports TEXTSPLIT when the postcode length may vary.
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. Open your spreadsheet: Open WPS Spreadsheets and select the workbook containing your addresses.
- 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. Enter the formula: Select an empty cell, enter =MID(A1,FIND(", ",A1,FIND(", ",A1)+2)+2,4), and press Enter.
- 4. Extract other postcodes: Drag the formula cell’s fill handle down to apply it to similarly formatted addresses.

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.




