Extracting Post Code and Locality

  • Good Morning All


    I have the a series of addresses each in a single field as follows


    [TABLE="width: 403"]

    [tr]


    [td]

    75 r Léon Boyer 37000 TOURS

    [/td]


    [/tr]


    [tr]


    [td]

    La Fredonnerie 37330 MARCILLY SUR MAULNE

    [/td]


    [/tr]


    [tr]


    [td]

    La Rabottière 37150 LUZILLE

    [/td]


    [/tr]


    [tr]


    [td]

    8 Bourg Neuf 37340 RILLE

    [/td]


    [/tr]


    [tr]


    [td]

    45 r Hospitalité 37000 TOURS

    [/td]


    [/tr]


    [tr]


    [td]

    23 pl Foire Le Roi 37000 TOURS

    [/td]


    [/tr]


    [tr]


    [td]

    15 Bis r Espérance 37600 BEAULIEU LES LOCHES




    I would like to extract the Post Code and the Locaility (as marked in red in the last address).


    I tried using the RIGHT and FIND functions but I have encountered two problems


    1. It does not seem to work as (for example if you limit the function to the first space) rom the 1st address it extracted "RS". It may be something in the format but cannot find anything wrong. I also checked the cell using the data to column wizard choosing space as a delimiter and "TOURS was extracted as one word.


    2. Given the type of locaity names the function which would apply for the 1st address will not work for the 2nd.



    Anyone can help.


    JamJ



    PS. Alternatively I can settle for a solution to inserting a hyphen before the postcode, and after use the data to column wizard.

    [/td]


    [/tr]


    [/TABLE]

  • Re: Extracting Post Code and Locality


    UDF


    =ZipCode(A1)


    to a Standard module

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

    Files

    • ZipCode.xlsm

      (14.53 kB, downloaded 68 times, last: )
  • Re: Extracting Post Code and Locality


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


    Code
    1. Function ExtractAddress(c As String) As String
    2. With CreateObject("VBScript.RegExp")
    3. .Pattern = "[\d]+[\D]+$"
    4. If .Test(c) Then ExtractAddress = .Execute(c)(0)
    5. End With
    6. End Function
  • 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