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 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.