UK Postal codes in Excel
 

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

UK Postal codes in Excel
 

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

UK Postal codes in Excel
 

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


"Paul G" <Paul wrote in message
...
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

UK Postal codes in Excel
 

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.
--
David Biddulph

"Stefi" wrote in message
...
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

UK Postal codes in Excel
 

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?

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.
--
David Biddulph

"Stefi" wrote in message
...
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

UK Postal codes in Excel
 

See this previous thread

http://tinyurl.com/yecyrp

--
HTH

Bob Phillips

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

"Paul G" <Paul wrote in message
...
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

UK Postal codes in Excel
 

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
InsertNameDefine
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,
DataValidationCustom =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.



--
Regards

Roger Govier


"Paul G" <Paul wrote in message
...
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