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.

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)


"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


How would I do this?

Any guidance gratefully received.

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/c...ediaId=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

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"
wrote:
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/c...ediaId=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