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

--
Regards

Roger Govier


"pseudonym" wrote in message
ups.com...
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.