Extracting a postal code from an address column into a separate column using formula

[Amir A]

Hi All

Could anyone please help to advise how to extract a postal code from an address column into a separate column using formula?

Here is a sample of what seems to be 4 different variations of the data:

JOHOR BAHRU JOHOR 80250 M'SIA </SPAN>

86800 MERSING JOHOR M'SIA </SPAN>

DI JALAN AMPANG </SPAN>

JALAN NB2 10/2 , TAMAN NUSA

<TBODY>
</TBODY>

The postal code is not in a fixed position as you can see in line 1(80250) and line 2(86800).
Line 3 does not have a postal code and line 4 doesn't either but it has other numbers(also not in a fixed position) which is part of a street name.

What seems to be consistent is that the postal code is always a 5 digit number.

Your advice would be very much appreciated.


Yours truly,
Amir A</SPAN>

 

[Rick Rothstein]

If none of the text has number with a dot or comma in them and if none of the text has an "E" surrounded by digits in them, then the following array-entered** formula should work...

=MID(A1,MIN(IF(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5)),ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1))+1,5)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Much better than what I just came up with (please see below). Thanks Rick.

Actually, I just thought of a way to fix the formula so the restrictions I mentioned are no longer a problem. As long as the text is all upper case as shown, this array-entered** formula should always work...

=MID(A1,MIN(IF(ISNUMBER(-MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".","X"),",","X"),"E","X"),ROW(INDIRECT("1:"&LEN(A1))),5)),ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1))+1,5)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

NOTE: I forgot to mention earlier, but unlike your formulas (which use the IFERROR function), my formula is not restricted to working in versions above XL2003 (I developed my formulas in XL2003 to create them).

 

[MARK858]

NOTE: I forgot to mention earlier, but unlike your formulas (which use the IFERROR function)

Hi Rick, only the formula in Column B uses IFERROR, The formula in Column C uses IF(ISNA so is 2003 compliant (just ugly and with too many lookups )

 

[Amir A]

Hmm, don't now why the BigNum changed in the 2003 version in my last post. It should have read as

Spreadsheet Formulas

Cell Formula C1 =IF(ISNA(TEXT(LOOKUP(9.99E+307,--MID(SUBSTITUTE(A1,"/","\"),MIN(FIND({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE(A1,"/","\")&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),0)),"",IF(LEN(TEXT(LOOKUP(9.99E+307,--MID(SUBSTITUTE(A1,"/","\"),MIN(FIND({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE(A1,"/","\")&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),0))=5,(TEXT(LOOKUP(9.99E+307,--MID(SUBSTITUTE(A1,"/","\"),MIN(FIND({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE(A1,"/","\")&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),0)),"")) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


But I'd still use Rick's version

Thanks so much to both Mark and Rick for the awesome solutions. Mark, your method worked on my work PC, using Excel 2007(sorry it isn't 2010 as I earlier mentioned). There was no error unlike my home 2003 version. Rick, your method worked too on Excel 2007, except that for the 3rd last record, BANDAR BUKIT PUCHONG 2, the number 2 was copied out as well, even though it was not a 5 digit number. Truly grateful for this, God bless both of you.

<tbody>
</tbody><colgroup><col></colgroup>

 

[Amir A]

Thanks so much to both Mark and Rick for the awesome solutions. Mark, your method worked on my work PC, using Excel 2007(sorry it isn't 2010 as I earlier mentioned). There was no error unlike my home 2003 version. Rick, your method worked too on Excel 2007, except that for the 3rd last record, BANDAR BUKIT PUCHONG 2, the number 2 was copied out as well, even though it was not a 5 digit number. Truly grateful for this, God bless both of you.

<tbody>
</tbody>

Hello there...could I please request for some help again. Noticed 3 things since the last time I used the formula. 1) For cells which have a postal code as well as a street or block no. in them, the postal code does not get extracted. Eg: 72-A JLN UNIVERSITI 46200 The result is a blank. 2) Cells which have a postal code which begins with 0 do not get extracted Eg: 05000 KUALA LUMPUR the result is a blank too. 3) Is there a way to extract 4 digit postal codes also by tweaking the formula? Have noticed there are some cells are like this UNI.ASIA 1008 JALAN SULTAN. Current result is a blank. And lastly 4) When there is a street or block no. together with what seems to be an incomplete postal code, it will result in forming a totally new number Eg:1-22-3 BELLEFIORE 205 will result in 44562. So sorry if some of these criteria were not mentioned earlier. It's kind of difficult to spot these abnormalities in a 80K + data. Hope for your kind assistance.

 

[István Hirsch]

*	A	B
1	JOHOR BAHRU JOHOR 80250 M'SIA	80250
2	86800 MERSING JOHOR M'SIA	86800
3	DI JALAN AMPANG	*
4	JALAN NB2 10/2 , TAMAN NUSA	*
5	BANDAR BUKIT PUCHONG 2	*
6	72-A JLN UNIVERSITI 46200	46200
7	05000 KUALA LUMPUR	05000
8	1-22-3 BELLEFIORE 205	*
9	JOHOR BAHRU 83025	83025
10	JOHUR BAHRU 00250	00250

<colgroup><col style="width:30px; "><col style="width:264px;"><col style="width:95px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This formula tries to avoid issues #1, #2 and #4 mentioned in thread #14. The formula should be array-entered into cell B1:

=IF(LOOKUP(10^5,1*MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("+0000 "&A1&" "," ","ß"),0,1),"-","ß"),ROW($A$1:$A$50),5))>
9999,TEXT(LOOKUP(10^5,1*MID(SUBSTITUTE(A1&" "," ","ß"),ROW($A$1:$A$50),5)),"00000"),"")

 

[Rick Rothstein]

*	A	B
1	JOHOR BAHRU JOHOR 80250 M'SIA	80250
2	86800 MERSING JOHOR M'SIA	86800
3	DI JALAN AMPANG	*
4	JALAN NB2 10/2 , TAMAN NUSA	*
5	BANDAR BUKIT PUCHONG 2	*
6	72-A JLN UNIVERSITI 46200	46200
7	05000 KUALA LUMPUR	05000
8	1-22-3 BELLEFIORE 205	*
9	JOHOR BAHRU 83025	83025
10	JOHUR BAHRU 00250	00250

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This formula tries to avoid issues #1, #2 and #4 mentioned in thread #14. The formula should be array-entered into cell B1:

=IF(LOOKUP(10^5,1*MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("+0000 "&A1&" "," ","ß"),0,1),"-","ß"),ROW($A$1:$A$50),5))>
9999,TEXT(LOOKUP(10^5,1*MID(SUBSTITUTE(A1&" "," ","ß"),ROW($A$1:$A$50),5)),"00000"),"")

First off, the formula I posted earlier does not work at all with some of the variations the OP has mentioned recently and I can not think of an effective way to fix it, so it should not be considered for use (I think the OP already discovered this). As for you latest formula.... very nice! However, I would point out one minor flaw (which I don't think will come up with the OP's indicated data, but I'll mention it just in case)... if the sole number in the text is 6 or more digits long, or if the last number in text with multiple numbers is 6 or more digits long (even if a 5-digit number appears earlier), your formula will grab the last 5 digits of that 6 or more digit number.

 

[István Hirsch]

Thanks, Rick. Yes, my formula in its present form can not handle numbers longer than 5 digits and this is a good occasion to draw the OP's attention to this fact. Also, as I realize now, $A$50 should be increased (for example, to $A$100 or more), just in case.

 

[Amir A]

Thanks, Rick. Yes, my formula in its present form can not handle numbers longer than 5 digits and this is a good occasion to draw the OP's attention to this fact. Also, as I realize now, $A$50 should be increased (for example, to $A$100 or more), just in case.

Hi Istvan and Rick, know it's been a few days and was not able to reply earlier. Just wanted to express my gratitude in providing the solutions, they are really helpful. Thanks Istvan for the formula and thanks Rick, noted on the small limitation in it(in fact have spotted some 6 digit postal codes in the dbase which are actually inaccuracies), will be weary of those types of entries.

 

[István Hirsch]

I tried to make the above formula available for 6 digits. Array-enter this into C1, and if everything goes well, you get a number (or blank cell) in col B (5 digit numbers) or C (6 digit numbers), that you can concatenate, for example, in col D:

=IF(LOOKUP(10^6,1*MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("+00000 "&A1&" "," ","ß"),0,1),"-","ß"),ROW($A$1:$A$100),6))>99999,TEXT(LOOKUP(10^6,1*MID(SUBSTITUTE(A1&" "," ","ß"),ROW($A$1:$A$100),6)),"000000"),"")

 

[Rick Rothstein]

Hi Istvan and Rick, know it's been a few days and was not able to reply earlier. Just wanted to express my gratitude in providing the solutions, they are really helpful. Thanks Istvan for the formula and thanks Rick, noted on the small limitation in it(in fact have spotted some 6 digit postal codes in the dbase which are actually inaccuracies), will be weary of those types of entries.

I know you asked for a formula originally, but how about a UDF (user defined function) instead (much easier to control the search using VBA code than with worksheet functions). The following function will retrieve 5-digit numbers only as long as they are either at the beginning, or at the end or, if internal, surrounded by space characters (which means your 6-digit numbers will be completely ignored).

Function GetZip(ByVal S As String) As String
  Dim X As Long
  For X = 2 To Len(S) + 2
    If Not Mid(" " & S & " ", X, 5) Like "*[!0-9]*" And _
       Mid(" " & S & " ", X - 1, 7) Like " ????? " Then
      GetZip = Mid(S, X - 1, 5)
      Exit Function
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetZip just like it was a built-in Excel function. For example,


=GetZip(A1)


If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.