• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract postal code from the address

tango

Member
I would like to extract the postal code from the hundreds of addresses we have on file. The postal code appears in the address in different postition (see sample below).

The postal code format starts with alphabet number alphabet number alphabet number (ie H9Y 2Z3) but sometimes written with dash or space or no dash in between ((ie H9Y 2Z3, B9C-4X3, C9C4X3)

Appreciate if someone could provide a formula to extract the postal code from the address please. Thank you for your help.


27 5Th Avenue North, H9Y 2Z3
150 Myroad Boulevard 408C, B9C-4X3, MyCity.
5Th Avenue, H9Y5X2, North Angel
750 Calif Boulevard 408C, H4A 2X3, Ariba, Deltacon
 
If you are using at least XL2019 then Flash Fill will do the trick for you.

 
Here is a formula solution to extract postal code from the address.

In B2, formula copied down :

=TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),99,99))

77089
 
Here is a formula solution to extract postal code from the address.

In B2, formula copied down :

=TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),99,99))

View attachment 77089

Thanks for your help Bosco and it works on some address with similar nomenclature/format of the address above however, results are not appropriate given different address/format below.

If I have few list of addresses, I can manually fix it however, I have hundreds of addresses and hopefully you can modify the formula to get the 'should be' postal code from the irregular address below that I have.

508 Saint Barn, 5007, H8Z 3B9, Pollan5007 (should be:H8Z 3B9)
294 Jo Rame, Vank H7U6P7Vank H7U6P7 (should be: H7U6P7)
215 Rivier, Unit 12, Z8Z 2B3, PiierUnit 12 (should be: Z8Z 2B3)
306 Boulevard Benwork, Apt. 104, Z7B 1N9, DauxApt. 104 (should be: Z7B 1N9)
200 Blvd Dewy, 125, H8B3Y6, Piier125 (should be: H8B3Y6)
Cascade, 4984, B7Z 1X2, Plankan4984 (should be: B7Z 1X2)
15 avillon, Apt. 3, Z8B 8Z2, FrankstonApt. 3 (should be: Z8B 8Z2)

Thanks for your help and support.
 
Back
Top