Validation of UK Postcodes all formats

[Engineerlee]

Hi
I am looking to validate UK postcodes in a similar fashion to another thread on here but it is quite old so thought I would generate a new one. Hopefully this isnt against the rules
UK Postal Code Validation

Hi, I am looking to validate UK Postal Code using MS Excel formula, can anyone help!!! The postal code is should be in the following format: AN NAA ANN NAA AAN NAA ANA NAA AANA NAA AANN NAA When A is alphabet and N is number. If the condition is incorrect then highlight that particular cell...

Can some one help
I am looking to do the same sort of validation including the CIKMOV check and illegal characters but with more postcode formats


My postcode field is always 9 characters in length
There are no spaces between the segments of the postcode - this would be considered incorrect and should report as such

Where a Postcode isnt the full 9 characters it is padded with spaces - these need to be also validated

AN[Space][Space][Space][Space][Space][Space][Space]
ANN[Space][Space][Space][Space][Space][Space]
AANA[Space][Space][Space][Space][Space]
ANNAA[Space][Space][Space][Space]
ANNNAA[Space][Space][Space]
AANNAA[Space][Space][Space]
ANANAA[Space][Space][Space]
AANANAA[Space][Space]
AANNNAA[Space][Space]
ANNAANA[Space][Space]
ANNNAANA[Space]
AANNAANA[Space]
ANANAANA[Space]
AANANAANA
AANNNAANA

Where A is alphabet character and N is number. [Space] is a physical space and not a word
If the condition is incorrect then highlight that particular cell that contains the incorrect postcode.
Many thanks in advance.
In reality they would look something like this

S1
S11
EC1A
S12BB
S112BB
LS12BB
W1A1BB
EC1A2BB
LS112BB
S12BB9Z
S112BB9Z
LS12BB9Z
W1A1BB9Z
EC1A2BB9Z
LS112BB9Z


Many thanks in advance

 

[mole999]

Did you try the function, it looks valid to me

 

[Engineerlee]

Hi Mole999

thanks for your prompt reply
No I havent as it isnt a function I am familiar with

Could you give any examples at all as I need to be able to validate all of the combinations with any legal Postcode characters
From what I understand CODE only returns the ASCII for the first Character.

Cheers

 

[mole999]

copy the provided function into the VBA section of the spreadsheet

The as instructed in an cell adjacent to the postcode type =the function name and cell reference as detailed, that will give you a response, you can drag that down the sheet

 

[Engineerlee]

Hi Mole999
Thanks for your return, and apologies as I didn't really understand your first post, however your second prompted me to look deeper at what I was looking to do.
you are right, I do get an output/response - which is great.. thankyou

There is one small issue........

It works with the exception of the last 2 examples

Postcode	Char Count	Output
S1	9​	TRUE​
S11	9​	TRUE​
EC1A	9​	TRUE​
S12BB	9​	TRUE​
S112BB	9​	TRUE​
LS12BB	9​	TRUE​
W1A1BB	9​	TRUE​
EC1A2BB	9​	TRUE​
LS112BB	9​	TRUE​
S12BB9Z	9​	TRUE​
S112BB9Z	9​	TRUE​
LS12BB9Z	9​	TRUE​
W1A1BB9Z	9​	TRUE​
EC1A2BB9Z	9​	#VALUE!​
DN112BB9Z	9​	#VALUE!​

If I add a space at the end then it returns a response, however the max characters allowed is 9 so these last 2 dont work unfortunately

is there a way to rectify this at all?

 

[mole999]

You have me at 9 characters, I can't think of any occurances in the postal world, even with this reporting true S112BB9Z its not valid last three is number letter letter

 

[Engineerlee]

The last 2 characters are a delivery point code so the example you have highlighted is valid with the last (9Z) being the delivery point
Does that help explain?

 

[mole999]

ah, OK, I only ever use the original part, so it could be safe to assume that if 9Z was eliminated and test against what is left