Format of Postcodes

[pseudonym]

I have a formula, but it 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?

Thanks.

 

[Guest]

My solution for the 6 digit codes slightly different than your for the 7
digit ones but try this:-

=IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),LEFT(A1,2)&"
"&MID(A1,3,1)&" "&MID(A1,4,3))

Mike

 

[pseudonym]

Why does this put a square symbol in the newly-created space?

 

[Guest]

It doesn't do it on my machine and If it does on yours then I'm confused!!
All it does is concatenate the 6 digit postcode with spaces. Any chance of a
paste of what it looks like.

Mike

 

[Pete_UK]

You are probably suffering from line-wrap if you pasted the formula
from the posting. The middle part of the formula should be:

.... LEFT(A1,2)&" "&MID(A1,3,1) ...

but you probably have a different character between the quotes.

Hope this helps.

Pete

 

[Dave Peterson]

An unfortunate line wrap:

=IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),
LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3))

(still one cell)

 

[Roger Govier]

Hi

It has been created by the wrapping of the formula in the NG reader.
This wrapping happens to take place in the middle of the space between
the double quotes.

After pasting the formula into the formula bar of your sheet, it will be
on 2 lines.
Place the cursor at the end of the first line and press Delete once.
That will bring the formula onto one line and the symbol will disappear.

Below I have reproduce Mike's formula, but forced the break to be after
the comma.

=IF(LEN(A1)=7,REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1))),
LEFT(A1,2)&" "&MID(A1,3,1)&" "&MID(A1,4,3))

If that were copied and pasted, even though it is on multiple lines, you
will not get the square symbol

 

[Guest]

Thanks people I thought I was having (another) elderly moment!!

 

[pseudonym]

Thanks everyone

Thanks people I thought I was having (another) elderly moment!!