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

Amir A

New Member
Joined
Mar 1, 2014
Messages
6
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>
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I am sure someone will come up with a better solution but until then try the formula below dragged down.

Excel Workbook
AB
1JOHOR BAHRU JOHOR 80250 M'SIA80250
286800 MERSING JOHOR M'SIA86800
3DI JALAN AMPANG
4JALAN NB2 10/2 , TAMAN NUSA
Sheet1
 
Last edited:
Upvote 0
I am sure someone will come up with a better solution but until then try the formula below dragged down.

Sheet1

*AB
1JOHOR BAHRU JOHOR 80250 M'SIA80250
286800 MERSING JOHOR M'SIA86800
3DI JALAN AMPANG*
4JALAN NB2 10/2 , TAMAN NUSA*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 253px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas
CellFormula
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?
</SPAN>
 
Upvote 0
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 .
 
Upvote 0
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.
 
Upvote 0
B1 = POST 2007 version
C1 =PRE 2007 version

Excel Workbook
ABC
1TAMAN MELAWATI   
2TAMAN GARDEN VILLA SUNWAY CITY
3PARK PRECINCT,BANDAR BOTANIC,
417 MILES SIBURAN SERIAN ROAD
5JALAN EASTERN PARK
631/166A KOTA KEMUNING HILLS 4167041670
7APT JALAN SERANTAU TMN DATO
831/98R KOTA KEMUNING SEKSYEN
9PSSR TITIWANGSA UTAMA TMN
10TELOK AIR TAWAR 13050 1305013050
11TROPICANA GOLF & COUNTRY
12BANDAR BUKIT PUCHONG 2
13SAUJANA RESORT SEK U2
14JALAN SUNGAI UJONG 70200 7020070200
Sheet1
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

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
ABC
1TAMAN MELAWATI   
231/986R KOTA KEMUNING SEKSYEN
3TELOK AIR TAWAR 13050 1305013050
4JALAN SUNGAI UJONG 70200 7020070200
Sheet1
 
Last edited:
Upvote 0
Hmm, don't now why the BigNum changed in the 2003 version in my last post. It should have read as

</table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></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<span style=' color:008000; '>(TEXT<span style=' color:#0000ff; '>(LOOKUP<span style=' color:#ff0000; '>(9.99E+307,--MID<span style=' color:#804000; '>(SUBSTITUTE<span style=' color:#ff7837; '>(A1,"/","\")</span>,MIN<span style=' color:#ff7837; '>(FIND<span style=' color:#8000ff; '>({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE<span style=' color:#545fa5; '>(A1,"/","\")</span>&1234567890)</span>)</span>,ROW<span style=' color:#ff7837; '>(INDIRECT<span style=' color:#8000ff; '>("1:"&LEN<span style=' color:#545fa5; '>(A1)</span>)</span>)</span>)</span>)</span>,0)</span>)</span>,"",IF<span style=' color:008000; '>(LEN<span style=' color:#0000ff; '>(TEXT<span style=' color:#ff0000; '>(LOOKUP<span style=' color:#804000; '>(9.99E+307,--MID<span style=' color:#ff7837; '>(SUBSTITUTE<span style=' color:#8000ff; '>(A1,"/","\")</span>,MIN<span style=' color:#8000ff; '>(FIND<span style=' color:#545fa5; '>({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE<span style=' color:008000; '>(A1,"/","\")</span>&1234567890)</span>)</span>,ROW<span style=' color:#8000ff; '>(INDIRECT<span style=' color:#545fa5; '>("1:"&LEN<span style=' color:008000; '>(A1)</span>)</span>)</span>)</span>)</span>,0)</span>)</span>=5,<span style=' color:#0000ff; '>(TEXT<span style=' color:#ff0000; '>(LOOKUP<span style=' color:#804000; '>(9.99E+307,--MID<span style=' color:#ff7837; '>(SUBSTITUTE<span style=' color:#8000ff; '>(A1,"/","\")</span>,MIN<span style=' color:#8000ff; '>(FIND<span style=' color:#545fa5; '>({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE<span style=' color:008000; '>(A1,"/","\")</span>&1234567890)</span>)</span>,ROW<span style=' color:#8000ff; '>(INDIRECT<span style=' color:#545fa5; '>("1:"&LEN<span style=' color:008000; '>(A1)</span>)</span>)</span>)</span>)</span>,0)</span>)</span>,"")</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top