How to extract postal code from the address

[tango]

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

 

[AlanSidman]

If you are using at least XL2019 then Flash Fill will do the trick for you.

Using Flash Fill in Excel - Microsoft Support

Have Excel automatically fill your data when it detects a pattern. For example, the months of the year, the days in a month, or a sequence of numbers. You can also parse data, like splitting first and last names out of a column, or concatenate data, like joining first and last names from two...

support.microsoft.com

 

[tango]

Thanks AlanSidman.

Tried it and helps a little. Hope there is an alternative please.

 

[pecoflyer]

Tried it and helps a little.

What do you lean? What goes wrong? Please provide a sheet with examples of errors?

 

[bosco_yip]

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

 

[tango]

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, Pollan	5007 (should be:H8Z 3B9)
294 Jo Rame, Vank H7U6P7	Vank H7U6P7 (should be: H7U6P7)
215 Rivier, Unit 12, Z8Z 2B3, Piier	Unit 12 (should be: Z8Z 2B3)
306 Boulevard Benwork, Apt. 104, Z7B 1N9, Daux	Apt. 104 (should be: Z7B 1N9)
200 Blvd Dewy, 125, H8B3Y6, Piier	125 (should be: H8B3Y6)
Cascade, 4984, B7Z 1X2, Plankan	4984 (should be: B7Z 1X2)
15 avillon, Apt. 3, Z8B 8Z2, Frankston	Apt. 3 (should be: Z8B 8Z2)

Thanks for your help and support.

 

[pecoflyer]

Please reply to post #4 please. Thanks