Extracting Post Code and Locality

Re: Extracting Post Code and Locality

JamJ
If they all start with 37 it not to hard but i asume that them may not

=MID(A1,FIND(" 37",A1)+1,50)&IF(ISERROR(MID(A1,FIND(" 37",A1,FIND(" 37",A1)+1)+1,50)),"",", "&MID(A1,FIND(" 03",A1,FIND(" 37",A1)+1)+1,50))

Re: Extracting Post Code and Locality

UDF

=ZipCode(A1)

to a Standard module

Function ZipCode(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\b\d{5}\D*$"
        ZipCode = .Execute(txt)(0)
    End With
End Function

Re: Extracting Post Code and Locality

or just

=IFERROR(MID(A5,FIND(" 37",A5)+1,50),"")

Re: Extracting Post Code and Locality

This seems to work for the sample given. Check Microsoft VBSript Regular Expressions 5.5 at Tools/References.

Function ExtractAddress(c As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[\d]+[\D]+$"
        If .Test(c) Then ExtractAddress = .Execute(c)(0)
    End With
End Function

Re: Extracting Post Code and Locality

If the post code are 5 digits and text in A1

in B1 =RIGHT(SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$35),1))*ROW($1:$35),0),ROW($1:$35))+1,1)*10^ROW($1:$35)/10),5)
and C1
=MID(A1,FIND(B1,A1)+5,50)

though jindon~san regx is very elegant

Re: Extracting Post Code and Locality

not a great solution , but may help while waiting for others to provide a better one

i found a formula which will extract the 5 digit number from a text string
and then modified to extract the number and all the text after the number
(LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1)

so a couple of issues
1) are all post code only 5 digitis ?
2) would any of the numbers at the start of the text house number ever be 5 numbers- if so this will not work

i'm sure others will offer a better solution

I then use find with the above formula to find the position of that text - add to a MID function so that the MID function will start with that character
and then use length - the position of that formula +1 to get the length of characters to extract from the start position of the 5 digit number

=MID(A1,FIND(LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1),LEN(A1)-FIND(LOOKUP(9.99E+307,LEFT(MID(A1,MATCH(TRUE,ISNUMBER(MID(SUBSTITUTE(SUBSTITUTE(A1,"/","@")," ","@"),ROW(A$1:INDEX(A:A,LEN(A1))),5)+0),0),15),ROW($1:$15))+0),A1,1)+1)
use control+shift+enter to use as an array and get {} around the formula