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.

 

[Peter_SSs]

Could you describe in words how to determine the postal code in a row of data?

Not being an expert in international postal codes, could you provide the expected results for that set of sample data?
I'm particularly interested in the results for rows 11, 16 and 25.

 

[excel_1317]

Could you describe in words how to determine the postal code in a row of data?

Not being an expert in international postal codes, could you provide the expected results for that set of sample data?
I'm particularly interested in the results for rows 11, 16 and 25.

Most Asian countries donot have postal codes and that is the case with row 16 where country is Vietnam. For row 11, postal code is in Column A i.e. 122 00. For row 25, Postal code is in column B i.e. 69448.

Here is the sample data with results.

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

Excel 2010

 

[excel_1317]

Plzzzzz help...

 

[Peter_SSs]

Thanks for providing the expected results and some further explanation. Unfortunately you didn't provide an answer to my first question.

Any solution in Excel will require some logical process(es) to extract the information.

I had thought that perhaps the logic might be to find the last numeric (possibly including dash or space) value from the row. However, that would not be the case for rows 3 (code includes a letter), 16 (neither of the numeric values is a postal code), 22 (neither last nor second last numeric is postal code) and 25 (it is the second last numeric that is the postal code. (I'm assuming your expected result for row 24 is a typo and the result should be 55431-1194?).

So unless some sort of rule(s) can be established for what is a postal code and what is not, I can't see how an Excel solution can be developed.

Given that the postal codes are scattered throughout the columns, if some method can be established, it would most likely have to be implemented by a macro so that would also have to be acceptable to you.

 

[Michael Simpson]

Unfortunately, I'm not going to come with a finished suggestion, but more some "worries".

First of all, are you 110% certain that the names of the countries in E will always be those values (by that I mean U.S.A. and not USA). Once you start getting variations on the names, it gets a a whole load more complicated.
Secondly, you didn't specify whether this should be solved by "excel programming" or whether you could write a VBA macro to handle it.

If a macro is acceptable, then the logic shouldn't be that difficult, but again, this assumes that the addresses you have don't contain any surprises (and I'll bet they will do at some time).

In fact, on reflection, I would probably go with the macro solution, since that will be easier to change for any "unexpected" variations in the data.

Reviewing your results, I see discrepancies in the results for lines 11 & 21. Line 11 uses column A to extract the postal code, line 21 uses column B and yet they're both Indian addresses. How on earth do you intend to solve this ? You'll end up with code (for India) that says, take the last 2 words from column A, are they numeric when catenated ?
Okay they weren't, then use column B. I can already see loads of exceptions to specific rules

 

[excel_1317]

Thanks for providing the expected results and some further explanation. Unfortunately you didn't provide an answer to my first question.

Any solution in Excel will require some logical process(es) to extract the information.

I had thought that perhaps the logic might be to find the last numeric (possibly including dash or space) value from the row. However, that would not be the case for rows 3 (code includes a letter), 16 (neither of the numeric values is a postal code), 22 (neither last nor second last numeric is postal code) and 25 (it is the second last numeric that is the postal code. (I'm assuming your expected result for row 24 is a typo and the result should be 55431-1194?).

So unless some sort of rule(s) can be established for what is a postal code and what is not, I can't see how an Excel solution can be developed.

Given that the postal codes are scattered throughout the columns, if some method can be established, it would most likely have to be implemented by a macro so that would also have to be acceptable to you.

To give a logic, we can concatenate the whole address leaving country column. From there a macro can be written on the basis of country name. For eg. When the country is U.S.A the the macro will look for only 5 digit numbers....

I need for 5 major countries.. I thought of a logic that can possibly be used..

Excel Workbook
	A	B	C
1	Country	Sample Postal Code	Logic
2	U.S.A.	92614	5 digit numeric
3	Israel	84965	5 digit numeric
4	Germany	D-70565	5 digit alphanumeric starting with D-
5	United Kingdom	HX7 7AF	6 characters alphabet with space after first 3 characters
6	France	31250	5 digit numeric
7	Italy	20131	5 digit numeric
Sheet5

Excel 2010

 

[Peter_SSs]

To give a logic, we can concatenate the whole address leaving country column. From there a macro can be written on the basis of country name. For eg. When the country is U.S.A the the macro will look for only 5 digit numbers....

I need for 5 major countries.. I thought of a logic that can possibly be used..

Excel Workbook
	A	B	C
1	Country	Sample Postal Code	Logic
2	U.S.A.	92614	5 digit numeric
3	Israel	84965	5 digit numeric
4	Germany	D-70565	5 digit alphanumeric starting with D-
5	United Kingdom	HX7 7AF	6 characters alphabet with space after first 3 characters
6	France	31250	5 digit numeric
7	Italy	20131	5 digit numeric
Sheet5

Excel 2010

OK, that's moving in the right direction at least but some further questions/comments.

1. The USA rule does not match with your expected result in row 17. Is the correct result for that row actually 10018?

2. Your logic for Germany does not appear to have any relevance to your expected results or the data in rows 7 or 22. Is there more we need to know there?

3. Haven't seen any sample data/results for UK or Italy so not sure about those.

In any case, see if this is any use.

Sub Extract_PC()
  Dim a
  Dim i As Long, j As Long, k As Long
  Dim rws As Long, numCountries As Long, CountryNum As Long
  Dim aCountries, aPCPatterns
  Dim RX As Object
  Dim s As String, PC As String
  
  Const Countries As String = "U.S.A.," & _
                              "Israel," & _
                              "Germany," & _
                              "United Kingdom," & _
                              "France," & _
                              "Italy"
                              
  Const PCPatterns As String = "\b\d{5}\b," & _
                              "\b\d{5}\b," & _
                              "\bD-\d{5}\b," & _
                              "\b[0-9A-Z]{3} [0-9A-Z]{3}\b," & _
                              "\b\d{5}\b," & _
                              "\b\d{5}\b,"
  
  aCountries = Split(Countries, ",")
  aPCPatterns = Split(PCPatterns, ",")
  numCountries = UBound(aCountries) + 1
  Set RX = CreateObject("VBScript.RegExp")
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 6)
    a = .Value
    rws = UBound(a, 1)
    For i = 1 To rws
      s = a(i, 5)
      CountryNum = 0
      For k = 1 To numCountries
        If s = aCountries(k - 1) Then
          CountryNum = k
        End If
      Next k
      If CountryNum > 0 Then
        RX.Pattern = aPCPatterns(CountryNum - 1)
        j = 4
        PC = ""
        Do
          s = a(i, j)
          If RX.test(s) Then
            PC = RX.Execute(s)(0)
          Else
            j = j - 1
          End If
        Loop While PC = "" And j > 0
        If Len(PC) > 0 Then
          a(i, 6) = PC
        End If
      End If
    Next i
    .Columns(6).NumberFormat = "@"
    .Value = a
  End With
End Sub

Results as follows (I've added one UK line):

Excel Workbook
	A	B	C	D	E	F
1	Attn Of/Address Line 1	Address Line 2	Address Line 3	Address Line 4	Address Line 5/Country Name	Result
2	2040 Main Street	14th Floor	Irvine, CA 92614		U.S.A.	92614
3	Ruppmannstrasse 27	D-70565 Stuttgart			Germany	D-70565
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.	22314
7	Bavariaring 20	80336 Munchen			Germany
8	67 Hayarkon str.	Bnei Brak 51206			Israel	51206
9	1185 Avenue of the Americas	New York, NY 10036			U.S.A.	10036
10	401 Congress Ave., Suite 3200	Austin, Texas 78701			U.S.A.	78701
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	84965
14	54 Derech Bet Lechem str.	Jerusalem 93504			Israel	93504
15	Lackenbach Siegel Building	One Chase Road	Scarsdale, New York 10583		U.S.A.	10583
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.	10018
18	Kibutz Lahav	Mobile Post Office 85335			Israel	85335
19	14 David Elazar str. 6	Beer Sheva 84509			Israel	84509
20	28 Hazait Str.	Meitar 85025			Israel	85025
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.	02142
24	1500 Norwest Financial Center	7900 Xerxes Avenue South	Bloomington, Minnesota	55431-1194	U.S.A.	55431
25	62 Rue de Bonnel	69448 Lyon Cedex 03			France	69448
26	123 Smith lane	Somewhere HX7 7AF			United Kingdom	HX7 7AF
27
Postal Codes

 

[excel_1317]

Thanks for this. I have copied the same in excel module. But how to use this?

OK, that's moving in the right direction at least but some further questions/comments.

1. The USA rule does not match with your expected result in row 17. Is the correct result for that row actually 10018?

2. Your logic for Germany does not appear to have any relevance to your expected results or the data in rows 7 or 22. Is there more we need to know there?

3. Haven't seen any sample data/results for UK or Italy so not sure about those.

In any case, see if this is any use.

Sub Extract_PC()
  Dim a
  Dim i As Long, j As Long, k As Long
  Dim rws As Long, numCountries As Long, CountryNum As Long
  Dim aCountries, aPCPatterns
  Dim RX As Object
  Dim s As String, PC As String
  
  Const Countries As String = "U.S.A.," & _
                              "Israel," & _
                              "Germany," & _
                              "United Kingdom," & _
                              "France," & _
                              "Italy"
                              
  Const PCPatterns As String = "\b\d{5}\b," & _
                              "\b\d{5}\b," & _
                              "\bD-\d{5}\b," & _
                              "\b[0-9A-Z]{3} [0-9A-Z]{3}\b," & _
                              "\b\d{5}\b," & _
                              "\b\d{5}\b,"
  
  aCountries = Split(Countries, ",")
  aPCPatterns = Split(PCPatterns, ",")
  numCountries = UBound(aCountries) + 1
  Set RX = CreateObject("VBScript.RegExp")
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 6)
    a = .Value
    rws = UBound(a, 1)
    For i = 1 To rws
      s = a(i, 5)
      CountryNum = 0
      For k = 1 To numCountries
        If s = aCountries(k - 1) Then
          CountryNum = k
        End If
      Next k
      If CountryNum > 0 Then
        RX.Pattern = aPCPatterns(CountryNum - 1)
        j = 4
        PC = ""
        Do
          s = a(i, j)
          If RX.test(s) Then
            PC = RX.Execute(s)(0)
          Else
            j = j - 1
          End If
        Loop While PC = "" And j > 0
        If Len(PC) > 0 Then
          a(i, 6) = PC
        End If
      End If
    Next i
    .Columns(6).NumberFormat = "@"
    .Value = a
  End With
End Sub

Results as follows (I've added one UK line):

Postal Codes

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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:208px;"><col style="width:181px;"><col style="width:181px;"><col style="width:154px;"><col style="width:131px;"><col style="width:69px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]92614[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]22314[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]51206[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]10036[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]78701[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]84965[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]93504[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]10583[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]10018[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]85335[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]84509[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]85025[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: right"]02142[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="align: right"]55431[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: right"]69448[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Thanks for this. I have copied the same in excel module. But how to use this?

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.