I am working in Excel 2002. The postal address is in cell A1 with the
postcode. I am looking to take the postcode out of the cell and put in
new
cell and turn into CAPITAL letters.
1 The High Street London ln22 4tb
I am struggling with the function for this. I am at =RIGHT(A1,8) which
takes the postcode from the end of the address and enters ino cell A2 but
I
thought i may have been able to add the UPPER to the end of the function
but
this does not work. I can do this as =UPPER(A2) in cell A3 but was hoping
to
undertake the augument in the same cell. Also can I adapt the augument to
include where I only have 6 characters ie LN24tb?
Many thanks
Roger
I thought it was a requirement that there be a <space> prior to the last 3
characters.
According to
http://www.zipcodeworld.com/addressing/greatbritain.htm
valid
postcodes are in one of the following formats:
The postcode will be in one of the six following formats. 'A' represents a
letter of the alphabet and 'N' a digit:
Format Example
AN NAA M2 5BQ
ANN NAA M34 4AB
AAN NAA CR0 2YR
AANN NAA DN16 9AA
ANA NAA W1A 4ZZ
AANA NAA EC1A 1HQ
Also, there are a finite number of postcode areas.
Depending on all of your requirements, it would be possible to at least
ensure
that the postcode is valid; it would also be possible to insert a <space>
if
one does not exist.
I would not want to develop a method to validate that the address and
postcode
go together, however. On that note I would point out that, according to
the
above mentioned site, LN refers to Lincoln, UK and not London, UK.
So, do you want any type of validation?
Will some of your postcodes NOT have the required <space>?
Will some of your address lines NOT have postcodes at the end?
In any event, the User Defined Function below will extract anything that
looks
similar to a valid postcode, with or without the required space, provided
it is
at the end of a line of text.
It will return the result capitalized, and also with the last three
characters
preceded by a <space>.
To enter this, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste
the
code below into the window that opens.
If GB develops new postal areas, some modification of the code below will
be
required, but that should be simple to do.
To use this, enter the formula =PostCode(cell_ref) where cell_ref refers
to a
single cell.
This code should work even if the entire address is in a single cell.
====================================================
Option Explicit
Function PostCode(Str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.MultiLine = True
.ignorecase = True
'Valid postcode format with optional <space>
.Pattern = "(\b\w{1,2}\d\w?)\s?(\d[ABD-HJLNP-UW-Z]{2}\b)\s*$"
End With
If re.test(Str) = True Then
Set mc = re.Execute(Str)
PostCode = UCase(mc(0).submatches(0) _
& " " & mc(0).submatches(1))
End If
End Function
===================================
--ron
