UK Postal codes in Excel

[Guest]

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

 

[Guest]

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

Regards,
Stefi

„Paul G†ezt írta:

 

[David Biddulph]

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)

 

[David Biddulph]

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.

 

[Guest]

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:

 

[Bob Phillips]

See this previous thread

http://tinyurl.com/yecyrp

--
HTH

Bob Phillips

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

 

[Roger Govier]

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.