Formatting Postcodes in VBA (Bob Philips, Myrna Larson + Jamie Collins?)

[Scott]

I have been using the code below which is a very slight adaptation of Myrna's original recommendation. As Jamie, kindly pointed out there are 8 exceptions to the UK Postcode system which start with just one alpha character! I need to be able to let these pass through, so basically I need to allow both the following codes...

"[A-Z][A-Z]## #[A-Z][A-Z]"
and
"[A-Z]## #[A-Z][A-Z]"

The 8 Codes that break the rule a begin with the following...

B - Birmingham
E - East London
G - Glasgow
L - Liverpool
M - Manchester
N - North London
S - Sheffield
W - West London

Hope this makes sense? Maybe once they have put the postcode in maybe a dialog box can confirm that the address is indeed "Manchester?" for example...


One more point I need addressing is that the first part of the postcode is sometimes a single figure. I will need for the code to put in a 0(zero) to cover this... For example their post code maybe SW4 2AP I need the program to realise that in order to format correctly it needs to add a 0 i.e. the correct results would be SW04 2AP. This is due to the users only putting it in lazily and not fully understanding the postal coding system.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As String

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("F2:F25000")) Is Nothing Then
With Target
X = UCase$(.Value)
If X Like "[A-Z][A-Z]0# #[A-Z][A-Z]" Then
'it's OK as is
ElseIf X Like "[A-Z][A-Z]0##[A-Z][A-Z]" Then
X = Left$(X, 4) & " " & Right$(X, 3)
Else
MsgBox "Incorrect format: AA0# #AA", vbOKOnly, "Error!"
'leave the string as-is so they can correct without
'retyping the whole thing
End If
.Value = X
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Hope you guys or somebody else can help me on this one. I'm more used to using vb.net and VBA is turning me around and I don't want to conflict my thoughts.

Scott

 

[Scott]

I have come across a one similar to this SW15A 4PP, but only once and from
that link you gave in the last post I noticed there is another. But I must
admit these must be very, very rare. I guess it would make the coding
infinitely more difficult if these 2 other variations are added. Must give
credit to Bob, as he has quite quickly generated some code.

Point taken though...

Scott

 

[Bob Phillips]

Nit if you use RegExp. They make such parsing simply (as long as you can
understand the expresssion )

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Actually considering your requirements, add the space, and cater for 1 digit
town numbers (add a 0), I don't think Regular Expressions save you much.

If the code is correct input, there are only 2 patterns to match in the
code that I gave
[A-Z][A-Z]## #[A-Z][A-Z]
[A-Z]## #[A-Z][A-Z]
so the saving on RegEx is minimal. All of the other patterns I gave are for
your particular format/output requirements.

I also tried some of the patterns provided in the link that Jamie gave, and
none was totally accurate in your definition of a valid postcode. I am sure
that they can be adapted to be 'perfect', but I don't feel confident enough
with patterns as of yet.

--

HTH

RP
(remove nothere from the email address if mailing direct)