Format of postcodes

[pseudonym]

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.

 

[Guest]

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
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[James Silverton]

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

 

[pseudonym]

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