We have several lists of postcodes in Excel, and they're all in different
formats like below. I've replaced the spaces with underscores.

L9_1AE
L9__1AE
LS9_1AE
LS191AE
L9___1AE
LS9__1AE
LS19_1AE

Without having to go through each and rewrite thousands of post codes, I
want to convert the existing data to a standard format of 8 characters like
the last example and was wondering if anyone had any ideas of formula I could
use to do this.

Thanks for your help in advance!

What are the rules for converting L9 to LS19?

You can use the TRIM() function to remove leading, trailing and multiple spaces

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dewheatley" wrote in message ...
| We have several lists of postcodes in Excel, and they're all in different
| formats like below. I've replaced the spaces with underscores.
|
| L9_1AE
| L9__1AE
| LS9_1AE
| LS191AE
| L9___1AE
| LS9__1AE
| LS19_1AE
|
| Without having to go through each and rewrite thousands of post codes, I
| want to convert the existing data to a standard format of 8 characters like
| the last example and was wondering if anyone had any ideas of formula I could
| use to do this.
|
| Thanks for your help in advance!
|
|
|

Thanks for your help, it's not quite what I'm looking for.

There are no rules for converting they are just examples of different
postcodes. Some can start as L9 or LS9 or L19 or any combination of letters
and numbers.

What I'm looking for is a way to make L9_6AE become L9___6AE or LS196AE
become LS19_6AE or the otehr variations so they are consistent. I've looked
up the final three characters with the RIGHT command, but what I want to do
(if possible!) is to get the first five characters without duplicating the
characters already obtained with the RIGHT command and add a space between
the two sections of the postcode before combining the two sections to an 8
character string.

"Niek Otten" wrote:

What are the rules for converting L9 to LS19?

You can use the TRIM() function to remove leading, trailing and multiple spaces

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dewheatley" wrote in message ...
| We have several lists of postcodes in Excel, and they're all in different
| formats like below. I've replaced the spaces with underscores.
|
| L9_1AE
| L9__1AE
| LS9_1AE
| LS191AE
| L9___1AE
| LS9__1AE
| LS19_1AE
|
| Without having to go through each and rewrite thousands of post codes, I
| want to convert the existing data to a standard format of 8 characters like
| the last example and was wondering if anyone had any ideas of formula I could
| use to do this.
|
| Thanks for your help in advance!
|
|
|

OK
I'll do it in a few steps. You can make it one ((very) large) formula if you like.

I suppose your codes are in Column A
In B1:
=IF(ISERROR(FIND(" ",A1)),6,FIND(" ",A1))
In C1:
=LEFT(A1,B1-1)
In D1:
=C1&REPT(" ",5-LEN(C1))
In E1:
=D1&RIGHT(A1,3) This is your final code

Copy B1:E1 down as far as you need

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dewheatley" wrote in message ...
| Thanks for your help, it's not quite what I'm looking for.
|
| There are no rules for converting they are just examples of different
| postcodes. Some can start as L9 or LS9 or L19 or any combination of letters
| and numbers.
|
| What I'm looking for is a way to make L9_6AE become L9___6AE or LS196AE
| become LS19_6AE or the otehr variations so they are consistent. I've looked
| up the final three characters with the RIGHT command, but what I want to do
| (if possible!) is to get the first five characters without duplicating the
| characters already obtained with the RIGHT command and add a space between
| the two sections of the postcode before combining the two sections to an 8
| character string.
|
| "Niek Otten" wrote:
|
| What are the rules for converting L9 to LS19?
|
| You can use the TRIM() function to remove leading, trailing and multiple spaces
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "dewheatley" wrote in message
...
| | We have several lists of postcodes in Excel, and they're all in different
| | formats like below. I've replaced the spaces with underscores.
| |
| | L9_1AE
| | L9__1AE
| | LS9_1AE
| | LS191AE
| | L9___1AE
| | LS9__1AE
| | LS19_1AE
| |
| | Without having to go through each and rewrite thousands of post codes, I
| | want to convert the existing data to a standard format of 8 characters like
| | the last example and was wondering if anyone had any ideas of formula I could
| | use to do this.
| |
| | Thanks for your help in advance!
| |
| |
| |
|
|
|

Hello, Niek!
You wrote on Fri, 3 Nov 2006 17:12:37 +0100:

NO I suppose your codes are in Column A
NO In B1:
NO =IF(ISERROR(FIND(" ",A1)),6,FIND(" ",A1))
NO In C1:
NO =LEFT(A1,B1-1)
NO In D1:
NO =C1&REPT(" ",5-LEN(C1))
NO In E1:
NO =D1&RIGHT(A1,3) This is your final code


I was intrigued by the problem but you beat me to an answer that
really works by a wide margin! The UK does not seem to have had
spreadsheets and computers in mind when they set up their
system. Come to think of it, the Canadians use something similar
so even I might have use for your prescription :-)


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Hello, dewheatley!
You wrote on Fri, 3 Nov 2006 07:46:02 -0800:

d There are no rules for converting they are just examples of
d different postcodes. Some can start as L9 or LS9 or L19 or
d any combination of letters and numbers.

As I said in a reply to Niek, UK post codes don't seem all that
well conditioned for computers! The British Post Office must
have some way of reading the things automatically and I wonder
if they use something like Niek's method? I tried Niek's
solution on all the possibilities listed by Wikipedia and it
works.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Hello, Niek!
You wrote on Fri, 3 Nov 2006 17:12:37 +0100:

NO I suppose your codes are in Column A
NO In B1:
NO =IF(ISERROR(FIND(" ",A1)),6,FIND(" ",A1))
NO In C1:
NO =LEFT(A1,B1-1)
NO In D1:
NO =C1&REPT(" ",5-LEN(C1))
NO In E1:
NO =D1&RIGHT(A1,3) This is your final code

NO Copy B1:E1 down as far as you need

NO --
NO Kind regards,

NO Niek Otten
NO Microsoft MVP - Excel

According to the British Post Office, the second group *can* be
preceeded by 2 figures, so the last code might best be
=D1&RIGHT(A1,4).

See
http://www.royalmail.com/portal/rm/c...ediaId=9200078


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

I believe you can use one simple formula.

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)

"dewheatley" wrote:

We have several lists of postcodes in Excel, and they're all in different
formats like below. I've replaced the spaces with underscores.

L9_1AE
L9__1AE
LS9_1AE
LS191AE
L9___1AE
LS9__1AE
LS19_1AE

Without having to go through each and rewrite thousands of post codes, I
want to convert the existing data to a standard format of 8 characters like
the last example and was wondering if anyone had any ideas of formula I could
use to do this.

Thanks for your help in advance!

Hello, daddylonglegs!
You wrote on Fri, 3 Nov 2006 11:25:01 -0800:

d If postcode is in A1

d =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1)))

d this will even deal with possibilities you haven't listed
d like

d L91AE (with no space)

d "dewheatley" wrote:

Sorry, but it does not seem to work with AA9A 19AA, which
according to the Royal Mail is possible :-(
Niek's code can be modified to work but I can't immediately see
what to do with yours. On the other hand, I've never actually
seen a post code like AA9A 19AA so the point may be moot.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Hi James,

I live in the UK and I'm not aware of any postcodes which have 4 characters
to the right of the space. Indeed wikipedia says:

"The inward code (the set of characters to the right of the space) must
always be 3 characters in length, the first numeric, the second and third
alphabetic"

"James Silverton" wrote:

Hello, daddylonglegs!
You wrote on Fri, 3 Nov 2006 11:25:01 -0800:

d If postcode is in A1

d =REPLACE(A1,LEN(A1)-2,0,REPT(" ",8-LEN(A1)))

d this will even deal with possibilities you haven't listed
d like

d L91AE (with no space)

d "dewheatley" wrote:

Sorry, but it does not seem to work with AA9A 19AA, which
according to the Royal Mail is possible :-(
Niek's code can be modified to work but I can't immediately see
what to do with yours. On the other hand, I've never actually
seen a post code like AA9A 19AA so the point may be moot.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Hello, daddylonglegs!
You wrote on Fri, 3 Nov 2006 15:40:02 -0800:

d I live in the UK and I'm not aware of any postcodes which
d have 4 characters to the right of the space. Indeed
d wikipedia says:

d "The inward code (the set of characters to the right of the
d space) must always be 3 characters in length, the first
d numeric, the second and third alphabetic"

I've never seen such a code either and the point may well be
moot but it is just that the Royal Mail site said it was
possible :-)


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

"James Silverton" wrote in message
...
Hello, daddylonglegs!
You wrote on Fri, 3 Nov 2006 15:40:02 -0800:

d I live in the UK and I'm not aware of any postcodes which
d have 4 characters to the right of the space. Indeed
d wikipedia says:

d "The inward code (the set of characters to the right of the
d space) must always be 3 characters in length, the first
d numeric, the second and third alphabetic"

I've never seen such a code either and the point may well be moot but it
is just that the Royal Mail site said it was possible :-)

As a matter of interest, where on the Royal Mail site does it say so?
--
David Biddulph

"This third part is the sector and is usually just one number."

http://www.postoffice.co.uk/portal/p...100267#3400055

David Biddulph wrote:
"James Silverton" wrote in message
...

Hello, daddylonglegs!
You wrote on Fri, 3 Nov 2006 15:40:02 -0800:

d I live in the UK and I'm not aware of any postcodes which
d have 4 characters to the right of the space. Indeed
d wikipedia says:

d "The inward code (the set of characters to the right of the
d space) must always be 3 characters in length, the first
d numeric, the second and third alphabetic"


I've never seen such a code either and the point may well be moot but it
is just that the Royal Mail site said it was possible :-)


As a matter of interest, where on the Royal Mail site does it say so?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks very much for this. It works a treat! I've read the discussions about
the 4 character part of the second half of the postcode, but it's not
something I'm aware of either.

While the Royal Mail website says it's usually like that, it doesn't give an
example, so is very ambiguous! Thanks to daddylonglegs and to Nick for their
help in this, it's been gratefully received!

"daddylonglegs" wrote:

I believe you can use one simple formula.

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)

"dewheatley" wrote:

We have several lists of postcodes in Excel, and they're all in different
formats like below. I've replaced the spaces with underscores.

L9_1AE
L9__1AE
LS9_1AE
LS191AE
L9___1AE
LS9__1AE
LS19_1AE

Without having to go through each and rewrite thousands of post codes, I
want to convert the existing data to a standard format of 8 characters like
the last example and was wondering if anyone had any ideas of formula I could
use to do this.

Thanks for your help in advance!

Niek

Thanks very much for this. It works great with the characters that contain a
space, though it's not quite worked on those without. The solution provided
by daddylonglegs has been useful as well. My thanks to you both.

"Niek Otten" wrote:

OK
I'll do it in a few steps. You can make it one ((very) large) formula if you like.

I suppose your codes are in Column A
In B1:
=IF(ISERROR(FIND(" ",A1)),6,FIND(" ",A1))
In C1:
=LEFT(A1,B1-1)
In D1:
=C1&REPT(" ",5-LEN(C1))
In E1:
=D1&RIGHT(A1,3) This is your final code

Copy B1:E1 down as far as you need

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dewheatley" wrote in message ...
| Thanks for your help, it's not quite what I'm looking for.
|
| There are no rules for converting they are just examples of different
| postcodes. Some can start as L9 or LS9 or L19 or any combination of letters
| and numbers.
|
| What I'm looking for is a way to make L9_6AE become L9___6AE or LS196AE
| become LS19_6AE or the otehr variations so they are consistent. I've looked
| up the final three characters with the RIGHT command, but what I want to do
| (if possible!) is to get the first five characters without duplicating the
| characters already obtained with the RIGHT command and add a space between
| the two sections of the postcode before combining the two sections to an 8
| character string.
|
| "Niek Otten" wrote:
|
| What are the rules for converting L9 to LS19?
|
| You can use the TRIM() function to remove leading, trailing and multiple spaces
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "dewheatley" wrote in message
...
| | We have several lists of postcodes in Excel, and they're all in different
| | formats like below. I've replaced the spaces with underscores.
| |
| | L9_1AE
| | L9__1AE
| | LS9_1AE
| | LS191AE
| | L9___1AE
| | LS9__1AE
| | LS19_1AE
| |
| | Without having to go through each and rewrite thousands of post codes, I
| | want to convert the existing data to a standard format of 8 characters like
| | the last example and was wondering if anyone had any ideas of formula I could
| | use to do this.
| |
| | Thanks for your help in advance!
| |
| |
| |
|
|
|

The only example of which I am aware that isn't of the form naa for the
second half of the code is TA1, which is preceded by SAN.
--
David Biddulph

"dewheatley" wrote in message
...
Thanks very much for this. It works a treat! I've read the discussions
about
the 4 character part of the second half of the postcode, but it's not
something I'm aware of either.

While the Royal Mail website says it's usually like that, it doesn't give
an
example, so is very ambiguous! Thanks to daddylonglegs and to Nick for
their
help in this, it's been gratefully received!

"daddylonglegs" wrote:

I believe you can use one simple formula.

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)

"dewheatley" wrote:

We have several lists of postcodes in Excel, and they're all in
different
formats like below. I've replaced the spaces with underscores.

L9_1AE
L9__1AE
LS9_1AE
LS191AE
L9___1AE
LS9__1AE
LS19_1AE

Without having to go through each and rewrite thousands of post codes,
I
want to convert the existing data to a standard format of 8 characters
like
the last example and was wondering if anyone had any ideas of formula I
could
use to do this.

Thanks for your help in advance!

Debra

I wrote to Royal Mail directly to find out if there were any postcodes with
four or more characters in the second part. I just got the reply:

'The inward code (the second part of the code) is only in theory capable of
being 4 characters long, we do not actually have any at the moment that
contain 4'

For the immediate future, the formula provided by DaddyLongLegs and by Niek
will work fine. Thanks again, guys!

"Debra Dalgleish" wrote:

"This third part is the sector and is usually just one number."

http://www.postoffice.co.uk/portal/p...100267#3400055

David Biddulph wrote:
"James Silverton" wrote in message
...

Hello, daddylonglegs!
You wrote on Fri, 3 Nov 2006 15:40:02 -0800:

d I live in the UK and I'm not aware of any postcodes which
d have 4 characters to the right of the space. Indeed
d wikipedia says:

d "The inward code (the set of characters to the right of the
d space) must always be 3 characters in length, the first
d numeric, the second and third alphabetic"


I've never seen such a code either and the point may well be moot but it
is just that the Royal Mail site said it was possible :-)


As a matter of interest, where on the Royal Mail site does it say so?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks for sharing that information!

dewheatley wrote:
Debra

I wrote to Royal Mail directly to find out if there were any postcodes with
four or more characters in the second part. I just got the reply:

'The inward code (the second part of the code) is only in theory capable of
being 4 characters long, we do not actually have any at the moment that
contain 4'

For the immediate future, the formula provided by DaddyLongLegs and by Niek
will work fine. Thanks again, guys!

"Debra Dalgleish" wrote:


"This third part is the sector and is usually just one number."

http://www.postoffice.co.uk/portal/p...100267#3400055

David Biddulph wrote:

"James Silverton" wrote in message
. ..


Hello, daddylonglegs!
You wrote on Fri, 3 Nov 2006 15:40:02 -0800:

d I live in the UK and I'm not aware of any postcodes which
d have 4 characters to the right of the space. Indeed
d wikipedia says:

d "The inward code (the set of characters to the right of the
d space) must always be 3 characters in length, the first
d numeric, the second and third alphabetic"


I've never seen such a code either and the point may well be moot but it
is just that the Royal Mail site said it was possible :-)


As a matter of interest, where on the Royal Mail site does it say so?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

David,

Surely that isn't a UK postcode, it must be somewhere in Lapland!

"David Biddulph" wrote:

The only example of which I am aware that isn't of the form naa for the
second half of the code is TA1, which is preceded by SAN.
--
David Biddulph

"dewheatley" wrote in message
...
Thanks very much for this. It works a treat! I've read the discussions
about
the 4 character part of the second half of the postcode, but it's not
something I'm aware of either.

While the Royal Mail website says it's usually like that, it doesn't give
an
example, so is very ambiguous! Thanks to daddylonglegs and to Nick for
their
help in this, it's been gratefully received!

"daddylonglegs" wrote:

I believe you can use one simple formula.

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)

"dewheatley" wrote:

We have several lists of postcodes in Excel, and they're all in
different
formats like below. I've replaced the spaces with underscores.

L9_1AE
L9__1AE
LS9_1AE
LS191AE
L9___1AE
LS9__1AE
LS19_1AE

Without having to go through each and rewrite thousands of post codes,
I
want to convert the existing data to a standard format of 8 characters
like
the last example and was wondering if anyone had any ideas of formula I
could
use to do this.

Thanks for your help in advance!

Belfast, according to http://news.bbc.co.uk/1/hi/business/1049531.stm :-)
--
David Biddulph

"daddylonglegs" wrote in message
...
David,

Surely that isn't a UK postcode, it must be somewhere in Lapland!

"David Biddulph" wrote:

The only example of which I am aware that isn't of the form naa for the
second half of the code is TA1, which is preceded by SAN.
--
David Biddulph

"dewheatley" wrote in message
...
Thanks very much for this. It works a treat! I've read the discussions
about
the 4 character part of the second half of the postcode, but it's not
something I'm aware of either.

While the Royal Mail website says it's usually like that, it doesn't
give
an
example, so is very ambiguous! Thanks to daddylonglegs and to Nick for
their
help in this, it's been gratefully received!

"daddylonglegs" wrote:

I believe you can use one simple formula.

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)

"dewheatley" wrote:

We have several lists of postcodes in Excel, and they're all in
different
formats like below. I've replaced the spaces with underscores.

L9_1AE
L9__1AE
LS9_1AE
LS191AE
L9___1AE
LS9__1AE
LS19_1AE

Without having to go through each and rewrite thousands of post
codes,
I
want to convert the existing data to a standard format of 8
characters
like
the last example and was wondering if anyone had any ideas of
formula I
could
use to do this.

Thanks for your help in advance!