Post Codes

  • Thread starter Thread starter Garry
  • Start date Start date
G

Garry

Hi all
When I receive postcodes such as CF205PN or CF25PN how can it be split too
CF20 5PN and CF2 5PN. The CF20 and CF2 are known as the outward code
while the 5NP is known as the inward code. The inward code is always
NUMERIC-ALPHA-ALPHA
any thoughts Garry
 
Do you want this is one field or two seperate fields

Anyway, assuming (as in England) that the inward code is always 3 character, you could do this

=left(pcode,len(pcode)-3) & " " & Right(pcode,3

This would put it in 1 field in the requested format

Depending on where you get your info from, you may want to consider that you might only get the outward code given - for this you could use a check on the length of code eg

=iif(len(pcode>5,left(pcode,len(pcode)-3) & " " & Right(pcode,3),pcode

In this case, if the pcode is 4 characters or less, it will assume that the inward code has not been provided and hence just give you what was provided rather than try and split it up. I used 4 characters or less because I think in the UK 5 characters is the minimum for a postcode (eg W1 5HU)

If you also get some with the spacing and some without, you can either search for a space in the text and act accordingly, or remove all spaces and run the above code

Let me know if you need any further help

Basi

----- Garry wrote: ----

Hi al
When I receive postcodes such as CF205PN or CF25PN how can it be split to
CF20 5PN and CF2 5PN. The CF20 and CF2 are known as the outward cod
while the 5NP is known as the inward code. The inward code is alway
NUMERIC-ALPHA-ALPH
any thoughts Garr
 
Thanks Basil that worked a treat

Basil said:
Do you want this is one field or two seperate fields?

Anyway, assuming (as in England) that the inward code is always 3 character, you could do this:

=left(pcode,len(pcode)-3) & " " & Right(pcode,3)

This would put it in 1 field in the requested format.

Depending on where you get your info from, you may want to consider that
Click to expand...
you might only get the outward code given - for this you could use a check
=iif(len(pcode>5,left(pcode,len(pcode)-3) & " " & Right(pcode,3),pcode)

In this case, if the pcode is 4 characters or less, it will assume that
Click to expand...
the inward code has not been provided and hence just give you what was
provided rather than try and split it up. I used 4 characters or less
because I think in the UK 5 characters is the minimum for a postcode (eg W1
5HU).
If you also get some with the spacing and some without, you can either
Click to expand...
search for a space in the text and act accordingly, or remove all spaces and
run the above code.
 
Back
Top