Postcode data

[IanT]

I have data which has a post code field. Some of the post codes do not have a space where they should ie. AB123CD instead of AB12 3CD. Is there code which I could run to update this to the correct format!
Thanks in advance!

 

[Mile-O]

How would you know where the Split comes in the post code?

 

[IanT]

How would you know where the Split comes in the post code?

Hi

Normaly it would be after the 4th number ie. PO12 4PR.

 

[Mile-O]

Yes, normally but my post code, as an example, is G46 and is only 6 digits in length. Those in Aberdeen can be eight digits.

The problem is further hampered by the fact that some post codes can be five characters long. (i.e. G1 25A)

I suppose you will need, then, to use the Right() function to get the last three characters so that you can begin with the Sector.

Then, knowing the Sector, you can get the Area and District by using the InStr() function to find at what position the Sector begins. Knowing this position, you can use the Left() function to get the Area and District.

i.e.

Public Function SplitCode(strPostal As String, booPart As Boolean) As String
    Dim intPosition As Integer
    Dim strCode As String
    strCode = Right(strPostal, 3)
    If booPart Then
        SplitCode = Left(strPostal, InStr(1, strPostal, strCode) - 1)
    Else
        SplitCode = strCode
    End If
End Function

To get the Area and District call the function like this:

SplitCode([PostCode], True)

and to get the Sector call the function like this:

SplitCode([PostCode], False)

Hopefully it will work as I've not tested it.

 

[DALeffler]

Picking a Nit...

Is there anything wrong with this:

Public Function SplitCode(strPostal As String, booPart As Boolean) As String
    SplitCode = Right(strPostal, 3)
    If booPart Then
        SplitCode = Left(strPostal, InStr(1, strPostal, SplitCode) - 1)
    End If
End Function

Thanks,

Doug.

 

[Mile-O]

Is there anything wrong with this:

Nope. That's just the exact same as what I said; just tidied.