ExcelBanter - Format of Postcodes

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Format of Postcodes (https://www.excelbanter.com/excel-discussion-misc-queries/143216-format-postcodes.html)

pseudonym

Format of Postcodes

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.

Mike H

Format of Postcodes

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.

pseudonym

Format of Postcodes

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.

Mike H

Format of Postcodes

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

"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.

Pete_UK

Format of Postcodes

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 -

Dave Peterson

Format of Postcodes

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)

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.

--

Dave Peterson

Roger Govier

Format of Postcodes

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.

Mike H

Format of Postcodes

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

"Mike H" wrote:

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

"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.

pseudonym

Format of Postcodes

Thanks everyone

On 18 May, 13:29, Mike H wrote:
Thanks people I thought I was having (another) elderly moment!!

"Mike H" wrote:
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

"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.

All times are GMT +1. The time now is 12:11 AM.