Extract Postal Codes from addresses

[excel_1317]

I have around 1000 addresses of about 60 different countries. These addresses are split into 5 columns. Need to extract postal codes in a new column.

Excel Workbook
	A	B	C	D	E
1	Attn Of/Address Line 1	Address Line 2	Address Line 3	Address Line 4	Address Line 5/Country Name
2	2040 Main Street	14th Floor	Irvine, CA 92614		U.S.A.
3	Ruppmannstrasse 27	D-70565 Stuttgart			Germany
4	Aeschenvorstadt 24	P.O. Box 318	4010 Basel		Switzerland
5	16, Akadimias str.	10671 Athens			Greece
6	1725 Duke St., Suite 240	Alexandria, VA 22314			U.S.A.
7	Bavariaring 20	80336 Munchen			Germany
8	67 Hayarkon str.	Bnei Brak 51206			Israel
9	1185 Avenue of the Americas	New York, NY 10036			U.S.A.
10	401 Congress Ave., Suite 3200	Austin, Texas 78701			U.S.A.
11	109, Sector 44, Gurgaon 122 00	National Capital Region			India
12	2-3 Fuji Bldg	Marunouchi 3-Chome	Chiyoda-Ku	Tokyo 100-0005	Japan
13	13 Haraduf Str.	Omer 84965			Israel
14	54 Derech Bet Lechem str.	Jerusalem 93504			Israel
15	Lackenbach Siegel Building	One Chase Road	Scarsdale, New York 10583		U.S.A.
16	Mail Box No. 15	Thai Thinh Post Office	35 Thai Thinh Street	Dong Da District, Hanoi	Vietnam
17	1040 Avenue of the Americas	New York, NY 10018-3738			U.S.A.
18	Kibutz Lahav	Mobile Post Office 85335			Israel
19	14 David Elazar str. 6	Beer Sheva 84509			Israel
20	28 Hazait Str.	Meitar 85025			Israel
21	B-6/10, Safdarjung Enclave,	New Delhi - 110 029			India
22	POSTFACH 11120	6900 HEIDELBERG 1			Germany
23	Riverfront Office Park	One Main Street	Eleventh Floor	Cambridge, MA 02142	U.S.A.
24	1500 Norwest Financial Center	7900 Xerxes Avenue South	Bloomington, Minnesota	55431-1194	U.S.A.
25	62 Rue de Bonnel	69448 Lyon Cedex 03			France
Sheet3

Excel 2010


Postal code can be in Column A, B C or D. Column E has the country. Please help me extracting the postal codes in a new column F.

 

[excel_1317]

With your data sheet active press Alt+F8, select the Extract_PC macro and click Run.

Make sure you test in a copy of your workbook.

Great!!!! It works like a charm. Your comments are correct. The logic doesnot work for all the cases because of data entry issues. But it is fine.. Thanks again..

 

[Peter_SSs]

Great!!!! It works like a charm. Your comments are correct. The logic doesnot work for all the cases because of data entry issues. But it is fine.. Thanks again..

I'm glad we got something useful.
I must admit that when I read your first post I wasn't expecting a good outcome, at least in such a short time.

If you can subsequently decide on any further country code patterns/rules like in post #7, they should be able to be added to the Countries and PCPatterns strings near the start of the code.

 

[ukmikeb]

Hi

I noticed that for the UK you expect a format of 7 characters with the 4th character being a space!
Quite the contrary see :-
Valid UK Postcode formats

The corresponding change to the Regex pattern would be:-

                              "\b[0-9A-Z]{2,4} [0-9A-Z]{3}\b," & _[\code]

hth

 

[Gerald Higgins]

http://en.wikipedia.org/wiki/Postal_codes_in_Vietnam