Another complicated one i think... sorry!

I have a column(G) on my activesheet containing a full
address and postcode. This is what I need to extract. I
would like to add it to a new column in the same row of
the original address Column(BN)

1) some rows do not have a postcode in the address
anywhere.
2) some of the postcodes are not the full postcodes i.e
LS3 or LS23 instead of LS23 4ZZ etc. They are in mix of
lower and UPPER case.
3) I need to extract all of the postcode not just the
first 3 letters

I think i need to go along the lines of telling the vb to
loop through a specified column G in the activesheet and
write the postcodes out to specified column in the same
sheet AS. Because of the format of the postcode i will
have to search for two letters then a number as some are
only LS3. I cant think of how I would collect the rest of
the postcode though.

Any help much appreciated.

Mary

Mary

You might use Outlook for this. It does a pretty good job of parsing US
addresses, but I don't have much experience with non-US ones. This sub
reads the full address from column A and returns the Postal Code to column
B. You need to set a reference (Tools - References) to the Outlook Object
Library.

Sub SplitPostCode()

Dim olApp As Outlook.Application
Dim olCI As Outlook.ContactItem
Dim rCell As Range

Set olApp = New Outlook.Application

For Each rCell In Sheet1.Range("a1:A3") 'change this
Set olCI = olApp.CreateItem(olContactItem)

With olCI
.BusinessAddress = rCell.Value
rCell.Offset(0, 1).Value = .BusinessAddressPostalCode
.Close olDiscard
End With
Next rCell

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Mary wrote:
Another complicated one i think... sorry!

I have a column(G) on my activesheet containing a full
address and postcode. This is what I need to extract. I
would like to add it to a new column in the same row of
the original address Column(BN)

1) some rows do not have a postcode in the address
anywhere.
2) some of the postcodes are not the full postcodes i.e
LS3 or LS23 instead of LS23 4ZZ etc. They are in mix of
lower and UPPER case.
3) I need to extract all of the postcode not just the
first 3 letters

I think i need to go along the lines of telling the vb to
loop through a specified column G in the activesheet and
write the postcodes out to specified column in the same
sheet AS. Because of the format of the postcode i will
have to search for two letters then a number as some are
only LS3. I cant think of how I would collect the rest of
the postcode though.

Any help much appreciated.

Mary

Hi Mary,

I'm afraid I haven't got a direct answer for you but did come across the
link below in an Access newsgroup which I hope proves useful.

All the best

John
http://groups.google.co.uk/groups?hl...ing.net&rnum=3

"Mary" wrote in message
...
Another complicated one i think... sorry!

I have a column(G) on my activesheet containing a full
address and postcode. This is what I need to extract. I
would like to add it to a new column in the same row of
the original address Column(BN)

1) some rows do not have a postcode in the address
anywhere.
2) some of the postcodes are not the full postcodes i.e
LS3 or LS23 instead of LS23 4ZZ etc. They are in mix of
lower and UPPER case.
3) I need to extract all of the postcode not just the
first 3 letters

I think i need to go along the lines of telling the vb to
loop through a specified column G in the activesheet and
write the postcodes out to specified column in the same
sheet AS. Because of the format of the postcode i will
have to search for two letters then a number as some are
only LS3. I cant think of how I would collect the rest of
the postcode though.

Any help much appreciated.

Mary