Extract a Post Code from an addres located in a cell

[Hanqaqa]

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

 

[mumps]

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:

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]

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:

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]

Hi!

Give a try to the formula below:

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

Markmzz

 

[Rick Rothstein]

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)

 

[mumps]

Glad to help.

 

[Hanqaqa]

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]

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]

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]

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