post code

[Guest]

am working with UK postcodes and need to strip out the area and district
automatically

The post code comes in following

A1 2BC
D34 5EF
GH6 7IJ
KL8M 9NQ


The part before the space contains the area and district.

The first is area which can be a single letter or double letter. The second
bit is usually a single number, however in london it can be a number and
letter.


So the results would be as follows.

A
D
GH
KL

How can I extract this automatically?

Many thanks

 

[Brendan Reynolds]

Public Function GetArea(ByVal PostCode As String) As String

'A1 2BC = A
'D34 5EF = D
'GH6 7IJ = GH
'KL8M 9NQ = KL

Dim strWork As String
Dim lngLoop As Long
Dim strChar As String

For lngLoop = 1 To InStr(1, PostCode, " ") - 1
strChar = Mid$(PostCode, lngLoop, 1)
If IsNumeric(strChar) Then
Exit For
Else
strWork = strWork & strChar
End If
Next lngLoop

GetArea = strWork

End Function

Public Function GetDistrict(ByVal PostCode As String) As String

'A1 2BC = 1
'D34 5EF = 34
'GH6 7IJ = 6
'KL8M 9NQ = 8M

Dim strWork As String
Dim lngLoop As Long
Dim strChar As String
Dim boolStart As Boolean

For lngLoop = 1 To InStr(1, PostCode, " ") - 1
strChar = Mid$(PostCode, lngLoop, 1)
If IsNumeric(strChar) Or boolStart Then
boolStart = True
strWork = strWork & strChar
End If
Next lngLoop

GetDistrict = strWork

End Function

Examples of use, in Immediate window ...

? getarea("A1 2BC")
A
? getarea("D34 5EF")
D
? getarea("GH6 7IJ")
GH
? getarea("KL8M 9NQ")
KL
? getdistrict("A1 2BC")
1
? getdistrict("D34 5EF")
34
? getdistrict("GH6 7IJ")
6
? getdistrict("KL8M 9NQ")
8M

 

[Guest]

Thanks
The "get area" works great. But the "getdistrict" needs some changes.

district is basically a combination of number & text before space.

like 'A1 2BC = A1

 

[Brendan Reynolds]

District is everything left of the space? In that case it's just
Left$(PostCode, InStr(1, PostCode, " ") - 1)

For example ...

? Left$("A1 2BC", InStr(1, "A1 2BC", " ") - 1)
A1