How To Extract Postcode From Address In Excel

During some of my work, I have the need to extract postcode from a list of client address which is saved in Microsoft Excel file. I need the postcode to print it on delivery note.

After some search I found the code snippet to extract postcode from address.

It’s very useful.

=IFERROR(--MID(H2,SUMPRODUCT(ISNUMBER(--MID(SUBSTITUTE(H2," ","×"),ROW(INDIRECT("1:"&LEN(H2))),5))(LEN(MID(SUBSTITUTE(H2," ","×"),ROW(INDIRECT("1:"&LEN(H2))),5))=5)ROW(INDIRECT("1:"&LEN(H2)))),5),"Postcode Not Found")

Just change H2 from the code above to your column number that contains the address that you wanted to extract the postcode from.

Note:

It seems that the code above does not work if there’s a dot after the postcode. But it works fine if there’s comma after the postcode.

Example:

These will work:

Felda Raja Alias, 72120 Bandar Seri Jempol, Negeri Sembilan.
Felda Raja Alias, Bandar Seri Jempol 72120, Negeri Sembilan.

These will not work:

Felda Raja Alias, Bandar Seri Jempol, Negeri Sembilan, 72120.
Felda Raja Alias, Bandar Seri Jempol 72120. Negeri Sembilan.

Make sure you’re formatting your address accordingly.

Leave a Comment