Format of postcodes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pseudonym - 03 May 2007 14:04 GMT

I have a column in a worksheet populated with postcodes. These
postcodes are either 6-character or 7-character strings.

Eg:
AB19PQ
PE548YT

I would like the spacing to be altered, so that they appear like this:
AB 1 9PQ
PE54 8YT

How would I do this?

Any guidance gratefully received.

Reply to this Message

John - 03 May 2007 14:22 GMT

one way put in =IF(LEN(A1)=7,LEFT(A1,4)&" "&(RIGHT(A1,3)),LEFT(A1,3)&"
"&(RIGHT(H5,3))) if the data is in cell A1

Signature

John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

> I have a column in a worksheet populated with postcodes. These
> postcodes are either 6-character or 7-character strings.
[quoted text clipped - 10 lines]
>
> Any guidance gratefully received.

Reply to this Message

James Silverton - 03 May 2007 15:12 GMT

John wrote on Thu, 3 May 2007 06:22:03 -0700:

J> Ice Hockey rules (especially the Wightlink Raiders)

J> "pseudonym" wrote:

??>> I have a column in a worksheet populated with postcodes.
??>> These postcodes are either 6-character or 7-character
??>> strings.
??>>
??>> Eg:
??>> AB19PQ
??>> PE548YT
??>>
??>> I would like the spacing to be altered, so that they
??>> appear like this: AB 1 9PQ PE54 8YT

British postcodes are complicated things (unnecessarily so,
IMHO)
http://www.royalmail.com/portal/rm/content1?catId=400120&mediaId=9200078

A while ago, "daddylonglegs" (Fri, 3 Nov 2006 ) came up in this
ng with

" If postcode is in A1

=REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1)))

this will even deal with possibilities you haven't listed like
L91AE (with no space)"

but the discussion went on for a long time to achieve a fully
comprehensive code.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Reply to this Message

pseudonym - 18 May 2007 11:40 GMT

Many thanks, this is closer to what I require, but the solution you
have offered does not quite meet with my needs.

=REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1)))

gives the result:

Eg:
AB19PQ
PE548YT

Changes it to:
AB1 9PQ
PE54 8YT

The 7-digit postcode is spaced correctly, but I would like an extra
space to appear in the 6-digit postcode i.e. AB 1 9PQ

Any ideas

-----

On 3 May, 15:12, "James Silverton" <not.jim.silver...@comcast.not>
wrote:

> John wrote on Thu, 3 May 2007 06:22:03 -0700:
>
[quoted text clipped - 34 lines]
> E-mail, with obvious alterations:
> not.jim.silverton.at.comcast.not

Reply to this Message