UK Postal codes in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul G - 29 Oct 2006 23:46 GMT

I would like to get help in creating a function to put into a cell with the
UK postal code, normally 2 letters then 1 or 2 numbers, space then 1 number
followed by 2 letters eg. WR9 9EP or WR10 3EH

Reply to this Message

Stefi - 30 Oct 2006 08:41 GMT

Make a search in "General questions" with keywords "postcode validation"!

Regards,
Stefi

„Paul G” ezt írta:

> I would like to get help in creating a function to put into a cell with the
> UK postal code, normally 2 letters then 1 or 2 numbers, space then 1 number
> followed by 2 letters eg. WR9 9EP or WR10 3EH

Reply to this Message

David Biddulph - 30 Oct 2006 09:10 GMT

As a matter of interest, where is this "General questions", to which you
refer, Stefi?

I can't see any group with that sort of name in the
microsoft.public.excel... hierarchy on the news server which I'm using, and
the msnews.microsoft.com server has no groups including the word "questions"
in the name.

Signature

David Biddulph

> Make a search in "General questions" with keywords "postcode validation"!
>
[quoted text clipped - 8 lines]
>> number
>> followed by 2 letters eg. WR9 9EP or WR10 3EH

Reply to this Message

Stefi - 30 Oct 2006 11:21 GMT

Well, I mean "General questions" group (forum) which appears clicking on
Communities in Excel Help, and there moving mouse cursor upon Excel. There
are a list of groups:
General Questions
New Users
Application Errors
Charts
Setup
Programming
Worksheet Functions (in which these posts are placed)

Regards,
Stefi

„David Biddulph” ezt írta:

> As a matter of interest, where is this "General questions", to which you
> refer, Stefi?
[quoted text clipped - 15 lines]
> >> number
> >> followed by 2 letters eg. WR9 9EP or WR10 3EH

Reply to this Message

David Biddulph - 30 Oct 2006 08:57 GMT

More generally, the format can be any of the following:
A9 9AA
A99 9AA
A9A 9AA
AA9 9AA
AA99 9AA
AA9A 9AA
(and 2 exceptional ones, GIR 0AA and SAN TA1)

Signature

David Biddulph

>I would like to get help in creating a function to put into a cell with the
> UK postal code, normally 2 letters then 1 or 2 numbers, space then 1
> number
> followed by 2 letters eg. WR9 9EP or WR10 3EH

Reply to this Message

Bob Phillips - 30 Oct 2006 11:29 GMT

See this previous thread

http://tinyurl.com/yecyrp

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Reply to this Message

Roger Govier - 30 Oct 2006 13:07 GMT

Hi Paul

Are you looking to apply Data Validation to cells to ensure that users
can only enter valid Postcodes?
If so, then assuming your data entry will be in column A first set up a
number of Named formulae with
Insert>Name>Define
Name First
Refers to =LEFT($A1,FIND(" ",$A1)-1)
Name Last
Refers to =RIGHT($A1,3)
Name start
Refers to =OR(ISTEXT(LEFT(first)),ISTEXT(LEFT(first,2)))
Name mid
Refers to =OR(ISTEXT(MID(first,3,1)),ISTEXT(MID(first,4,1)))
Name end
Refers to =AND(ISNUMBER(--(LEFT(last))),ISTEXT(RIGHT(last,2)))
Name numbers
Refers to
=AND(OR(ISNUMBER(--(MID(first,2,1))),ISNUMBER(--(MID(first,2,2))),
ISNUMBER(--(MID(first,3,1))),ISNUMBER(--(MID(first,3,2)))),
NOT(ISNUMBER(--(RIGHT(first,3)))))

Mark the range of cells in column A where you want the users to enter
Postcode,
Data>Validation>Custom> =AND(start,mid,end,numbers)
Remove tick mark from ignore Blank
Got to Error Alert tab and ensure there is a tick mark in Show error
Alert. Type a message here if you wish.

If not for column A, then change all references from A to the relevant
column letter.

Signature

Regards

Roger Govier

>I would like to get help in creating a function to put into a cell with
>the
> UK postal code, normally 2 letters then 1 or 2 numbers, space then 1
> number
> followed by 2 letters eg. WR9 9EP or WR10 3EH

Reply to this Message