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

On May 18, 12:27 pm, pseudonym wrote:
Why does this put a square symbol in the newly-created space?

On 18 May, 12:21, Mike H wrote:



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" wrote:
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.- Hide quoted text -

- Show quoted text -