none
Validate and amend errors in row of UK postcodes RRS feed

  • Question

  • Hello, 

    Please help. 

    I have a .csv file which contains a column of postcodes (M). I need a sub which will go through that column line by line, validate that the postcode is one of the UK formats and amend some basic format errors which we are seeing in the data.

    The UK postcode is made up of 2 parts. The second part is always 3 characters. The first part may contain between 2-4 characters in the following formats-

    (A=Alpha, N= Numeric)

    AANN

    AAN

    ANN

    AN

    In row M of my csv file the postcodes are held as one string (not in 2 sections)

    I would like to fix the following common errors-

    1) Spaces in between the postcode elements- easy, I can do this with a simple find and remove. 

    2) Zeroes added in the wrong place (this is due to the way the data is held in the source system. This is the bit I can't get it to do...

    Essentially the source system holds postcodes with zeroes in the following formats (don't ask me why)...

    AAN = AA0N so for example, SE1 is held as SE01, but I want to convert it back to SE1 with some kind of validation so that for example the postcode SE10 1NY (not sure if this is valid, bit it is an example) remains untouched (I did a find and replace for the leading zeroes- for example "01" to "1" but this changed the postcode above to SE1 1NY, which I do not want to do.)

    AN = A0N so for example N5 is held as N05, but I want to convert back to N5 the same as above. 

    Ideally I'd like this to be a sub I can call from the main .csv conversion sub I have written.

    Is anyone able to help?

    Thanks.

    Thursday, July 4, 2013 4:32 PM

Answers

  • The rules for GB Post codes are quite a bit more complex than what you have written, as there are characters that are allowed/disallowed depending on the position.

    In any event, here is a routine that

    • examines column M
    • inserts the <space> prior to the last three characters
    • tests the first part to see if there is a sequence of 0N and, if there is, removes the 0.
    • test the result to see if it meets the rules for a valid post code using a Regular Expression.
    • If the code is valid, writes the result in Column Z (or whatever column you use for rDest.

    If it works, you can change it to suit; e.g. replace the original data, by changing rDest in the macro.

    =====================================
    Option Explicit
    Sub UKPostCodeValidator()
        Dim re As Object
        Const sPat As String = "^([A-PR-UWYZ0-9][A-HK-Y0-9][AEHMNPRTVXY0-9]?[ABEHMNPRVWXY0-9]? {1,2}[0-9][ABD-HJLN-UW-Z]{2}|GIR 0AA)$"
        Dim vSrc As Variant, vRes() As Variant, v As Variant
        Dim rDest As Range
        Dim i As Long, s As String

    vSrc = Range("M1", Cells(Rows.Count, "M").End(xlUp))
    Set rDest = Range("Z1").Resize(rowsize:=UBound(vSrc))
        rDest.EntireColumn.Clear
    ReDim vRes(LBound(vSrc) To UBound(vSrc), 1 To 2)

    Set re = CreateObject("VBSCRIPT.REGEXP")
    With re
        .Global = False
        .ignorecase = False
        .Pattern = sPat
    End With

    For i = LBound(vSrc) To UBound(vSrc)
        s = vSrc(i, 1)
        s = Left(s, Len(s) - 3) & " " & Right(s, 3)
        v = Split(s)
        If v(0) Like "*0#*" Then v(0) = Replace(v(0), "0", "")
        s = Join(v)
        If re.test(s) = True Then vRes(i, 1) = s
    Next i

    rDest = vRes

    End Sub
    ============================================


    Ron

    Saturday, July 6, 2013 7:04 PM

All replies

  • The rules for GB Post codes are quite a bit more complex than what you have written, as there are characters that are allowed/disallowed depending on the position.

    In any event, here is a routine that

    • examines column M
    • inserts the <space> prior to the last three characters
    • tests the first part to see if there is a sequence of 0N and, if there is, removes the 0.
    • test the result to see if it meets the rules for a valid post code using a Regular Expression.
    • If the code is valid, writes the result in Column Z (or whatever column you use for rDest.

    If it works, you can change it to suit; e.g. replace the original data, by changing rDest in the macro.

    =====================================
    Option Explicit
    Sub UKPostCodeValidator()
        Dim re As Object
        Const sPat As String = "^([A-PR-UWYZ0-9][A-HK-Y0-9][AEHMNPRTVXY0-9]?[ABEHMNPRVWXY0-9]? {1,2}[0-9][ABD-HJLN-UW-Z]{2}|GIR 0AA)$"
        Dim vSrc As Variant, vRes() As Variant, v As Variant
        Dim rDest As Range
        Dim i As Long, s As String

    vSrc = Range("M1", Cells(Rows.Count, "M").End(xlUp))
    Set rDest = Range("Z1").Resize(rowsize:=UBound(vSrc))
        rDest.EntireColumn.Clear
    ReDim vRes(LBound(vSrc) To UBound(vSrc), 1 To 2)

    Set re = CreateObject("VBSCRIPT.REGEXP")
    With re
        .Global = False
        .ignorecase = False
        .Pattern = sPat
    End With

    For i = LBound(vSrc) To UBound(vSrc)
        s = vSrc(i, 1)
        s = Left(s, Len(s) - 3) & " " & Right(s, 3)
        v = Split(s)
        If v(0) Like "*0#*" Then v(0) = Replace(v(0), "0", "")
        s = Join(v)
        If re.test(s) = True Then vRes(i, 1) = s
    Next i

    rDest = vRes

    End Sub
    ============================================


    Ron

    Saturday, July 6, 2013 7:04 PM
  • Fantastic, thanks so much.

    Monday, July 8, 2013 1:35 PM