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