Standardising Postcodes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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

| 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
| >
| > | 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/content1?catId=400120&mediaId=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)
 
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"
 
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 said:
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"
Click to expand...
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 :-)
Click to expand...

As a matter of interest, where on the Royal Mail site does it say so?
 
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!
 
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.
 
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.
 
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!
 
Thanks for sharing that information!
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!

:
Click to expand...
 
Back
Top