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

Amir A · Mar 2, 2014

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:
[TABLE="width: 255"]
<TBODY>[TR]
[TD]JOHOR BAHRU JOHOR 80250 M'SIA 
[/TD]
[/TR]
[TR]
[TD]86800 MERSING JOHOR M'SIA 
[/TD]
[/TR]
[TR]
[TD]DI JALAN AMPANG 
[/TD]
[/TR]
[TR]
[TD]JALAN NB2 10/2 , TAMAN NUSA
[/TD]
[/TR]
</TBODY>[/TABLE]

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

MARK858 · Mar 2, 2014

I am sure someone will come up with a better solution but until then try the formula below dragged down.

Excel Workbook

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

Sheet1

Amir A · Mar 2, 2014

MARK858 said:
I am sure someone will come up with a better solution but until then try the formula below dragged down.

Sheet1

* A B
JOHOR BAHRU JOHOR 80250 M'SIA
86800 MERSING JOHOR M'SIA
DI JALAN AMPANG *
JALAN NB2 10/2 , TAMAN NUSA *

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 253px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

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

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

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

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

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

</TBODY>

Spreadsheet Formulas

Cell Formula
B1 =IFERROR(IF(LEN(TEXT(LOOKUP(9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),0))=5,TEXT(LOOKUP(9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),0),""),"")

<TBODY>
</TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Hi MARK858,

Wow, what a formula. Thanks very much, am so grateful.

But there was this error when i tried it.

At this porton of the formula, ROW(INDIRECT("1:"&LEN(A1))))),0))=5,

LEN was highlighted and below it, is shown the message INDIRECT(ref_text,[a1])

Any ideas how to rectify this?

MARK858 · Mar 2, 2014

Not really as my post is an exact copy of my spreadsheet and doesn't error on my spreadsheet.

Did you copy and paste the formula or did you re-type it?

If you did copy and paste the formula without making any alterations then the only thing I can suggest is uploading your spreadsheet to a free file hosting site like www.Box.com, make you you have set the file for sharing and then post the link it provides in the thread so I can take a look at it at some stage .

Amir A · Mar 2, 2014

MARK858 said:
Not really as my post is an exact copy of my spreadsheet and doesn't error on my spreadsheet.

Did you copy and paste the formula or did you re-type it?

If you did copy and paste the formula without making any alterations then the only thing I can suggest is uploading your spreadsheet to a free file hosting site like www.Box.com, make you you have set the file for sharing and then post the link it provides in the thread so I can take a look at it at some stage .

Sorry for the late reply, yes I copy and pasted the formula.
By the way, am using Excel 2003 at home, don't suppose it has anything to do with it?
In any case, will be using the actual formula on Excel 2010 at work later on.

Have uploaded the sample to this link:

https://app.box.com/s/wus50p16sqporc0jc1v3

Filename: extracting postal code sample with formula.xlsx

Thanks a lot for your time.

MARK858 · Mar 2, 2014

B1 = POST 2007 version
C1 =PRE 2007 version

Excel Workbook

A

B

C

1

TAMAN MELAWATI

2

TAMAN GARDEN VILLA SUNWAY CITY

3

PARK PRECINCT,BANDAR BOTANIC,

4

17 MILES SIBURAN SERIAN ROAD

5

JALAN EASTERN PARK

6

31/166A KOTA KEMUNING HILLS

41670

7

APT JALAN SERANTAU TMN DATO

8

31/98R KOTA KEMUNING SEKSYEN

9

PSSR TITIWANGSA UTAMA TMN

10

TELOK AIR TAWAR 13050

13050

11

TROPICANA GOLF & COUNTRY

12

BANDAR BUKIT PUCHONG 2

13

SAUJANA RESORT SEK U2

14

JALAN SUNGAI UJONG 70200

70200

Sheet1

MARK858 · Mar 2, 2014

Ah I see an issue that with row 6 as it looks like it is interpreting that as a date. afraid I don't have a work around for that at present (not without a find replace on the / in the original data anyway)

I will post back if I think of anything.

Rick Rothstein · Mar 2, 2014

MARK858 said:
Ah I see an issue that with row 6 as it looks like it is interpreting that as a date. afraid I don't have a work around for that at present (not without a find replace on the / in the original data anyway)

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

MARK858 · Mar 2, 2014

Rick Rothstein said:
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.

Excel Workbook

A

B

C

1

TAMAN MELAWATI

2

31/986R KOTA KEMUNING SEKSYEN

3

TELOK AIR TAWAR 13050

13050

4

JALAN SUNGAI UJONG 70200

70200

Sheet1

MARK858 · Mar 2, 2014

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

</table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=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)),""))</td></tr></table></td></tr></table> Excel tables to the web >> <a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

But I'd still use Rick's version

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

Amir A

New Member

Excel Facts

MARK858

MrExcel MVP

Amir A

New Member

MARK858

MrExcel MVP

Amir A

New Member

MARK858

MrExcel MVP

MARK858

MrExcel MVP

Rick Rothstein

MrExcel MVP

MARK858

MrExcel MVP

MARK858

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock