Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How to enter codes in excel and have corresponding payment sum ? | New Users to Excel | |||
Distances between Postal Codes | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
Excel Error codes | Excel Discussion (Misc queries) |