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

 

[Hanqaqa]

Thank you Markmzz, those two also work perfectly. I can only work with the comma one, since I have the european Excel, I guess that is the reason.

I also had to change commas, to semicolon ; in between the excel commands if anyone finds that useful as follows.

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

 

[markmzz]

Thank you Markmzz, those two also work perfectly. I can only work with the comma one, since I have the european Excel, I guess that is the reason.

I also had to change commas, to semicolon ; in between the excel commands if anyone finds that useful as follows.

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

You are welcome.

I'm glad to help and thank you for the feedback and for your version of the formula to help others users.

Markmzz

 

[voodoo1]

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

Worked like a charm!!

Applied it to my case where the postal code is ####-###
Also adapted the same code, and run it after for the postal codes with format #### (which I run second to not exclude the first case)

Thank you mumps!

 

[mumps]

Glad it worked out for you.