Extract a Post Code from an addres located in a cell

Hanqaqa

New Member
Joined
Mar 2, 2018
Messages
5
Hello great guys from MrExcel, I have been lurking this forum forum for a few months as it has always been very very helpful.

But today I have a problem that I can't get to solve. I have a list of addresses in column A, that aren't quite well structured. And I need to get their post codes in column B. A few examples are

Calle Reyes Catolicos, 159, 18009 Granada
N-323A Km138.4 Calle Ogijares 18151 Ogijares, Granada
Calle Moraleda 18 3B Cajar 18199
Plaza de España, 1, 18270 MontefrÃ*o

What I would like to get in column B is

18009
18151
18199
18270

The postcode in my city ranges from 18000 to 18999 if it is any help

There are many tutorials on the internet on how to do it if the postcode is always fixed or after a comma in the same place using the =FIND or =LEFT =SEARCH commands, but those are no use for me as the position of the postcode keeps changing.

There is this thread in MrExcel where people use it with London's postcodes
https://www.mrexcel.com/forum/excel-questions/655611-identify-extract-postcode-address.html
but I am having difficulties addapting it to my needs. If anyone could guide me in the right direction I would be forever grateful
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,640
Assuming that the Post Codes are always 5 digits and that if there are other 5 digit numbers in the address, that the Post Code is the last 5 digit numner:
Code:
Sub GetCode()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim splitRng As Variant
    Dim i As Long
    For Each rng In Range("A1:A" & LastRow)
        splitRng = Split(rng, " ")
        For i = LBound(splitRng) To UBound(splitRng)
            If splitRng(i) Like "#####" Then
                rng.Offset(0, 1) = splitRng(i)
            End If
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub
 

Hanqaqa

New Member
Joined
Mar 2, 2018
Messages
5
Assuming that the Post Codes are always 5 digits and that if there are other 5 digit numbers in the address, that the Post Code is the last 5 digit numner:
Code:
Sub GetCode()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim splitRng As Variant
    Dim i As Long
    For Each rng In Range("A1:A" & LastRow)
        splitRng = Split(rng, " ")
        For i = LBound(splitRng) To UBound(splitRng)
            If splitRng(i) Like "#####" Then
                rng.Offset(0, 1) = splitRng(i)
            End If
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub


Oh wow that works great, it got all the 900 addresses perfectly. Many many thanks.

I would still be interested if there is an answer where the function that gets the postal code is not written in VBA, but a function inside a cell in Excel. Since it is what I use most of the time and would like to learn if it is possible, since I can't get my mind around it.
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Hi!

Give a try to the formula below:

=MID(A2,LOOKUP(8^7,SEARCH(ROW(18000:18999),A2)),5)

Markmzz
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,547
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I would still be interested if there is an answer where the function that gets the postal code is not written in VBA, but a function inside a cell in Excel. Since it is what I use most of the time and would like to learn if it is possible, since I can't get my mind around it.
Given your postal codes all start with 18 and are 5 digits long and assuming the postal code is always the last number in the cell...

=18&LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"18",REPT(" ",99)),99)),3)
 

Hanqaqa

New Member
Joined
Mar 2, 2018
Messages
5

ADVERTISEMENT

Hi!

Give a try to the formula below:

=MID(A2,LOOKUP(8^7,SEARCH(ROW(18000:18999),A2)),5)

Markmzz

That is precisely what I was looking for. Thank you very much markmzz
 

Hanqaqa

New Member
Joined
Mar 2, 2018
Messages
5
Given your postal codes all start with 18 and are 5 digits long and assuming the postal code is always the last number in the cell...

=18&LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"18",REPT(" ",99)),99)),3)

Oh thank you very much Rick for adapting your code from the post, I was having some trouble adapting it to my problem, but now I see it more clearly.

Thank you all for the prompt and clear replies. :):):)
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
Hi!

Give a try to the formula below:

=MID(A2,LOOKUP(8^7,SEARCH(ROW(18000:18999),A2)),5)

Markmzz

This approach may be useful if the range is small, but in this case processing each cell needs counting up to 1000 (from 18000 to 18999, up to 18550 as an average), so the processing time is very long.
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
That is precisely what I was looking for. Thank you very much markmzz

You are welcome and I'm happy to help.

And thank you for the feedback.

By the way, I need to do a small modification in my suggestion:

=MID(A2,LOOKUP(8^7,SEARCH(ROW($18000:$18999),A2)),5)

And try this too (if you want):

=LOOKUP(1,1/(MID(A2,SEARCH("18",A2,ROW($1:$99)),5)&"."),
MID(A2,SEARCH("18",A2,ROW($1:$99)),5))


Or

=LOOKUP(1,1/(MID(A2,SEARCH("18",A2,ROW($1:$99)),5)&","),
MID(A2,SEARCH("18",A2,ROW($1:$99)),5))


Markmzz
 

Forum statistics

Threads
1,147,635
Messages
5,742,245
Members
423,717
Latest member
rubthenut

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
Top