How do I list postcodes in order in Excel?

[jillysillybilly]

I have a list in Excel that I need to sort by postcodes, but in its current
format, the postcodes (Column A) go like this, eg: BA1 then BA10 BA11 BA12
etc then BA2, but I want them to go BA1 BA2 BA3 etc. How do I do this?

[Dave O]

Please excuse my ignorance: are post codes always in the format
alpha-alpha-number-number-number?

Excel is treating these entries as text because of the BA characters.
If you separate the alpha characters from the numerics into two
different columns, you can sort on the different columns to get your
desired results. So if you enter two new columns, you can parse the
alpha characters into one column using this formula (assuming that post
codes always have two leading alpha characters, which was the genesis
behind my original question):
=MID(A1,1,2)
.... where A1 is a post code.

Then get the numeric portion in a new column with this formula:
=MID(A1,3,LEN(A1))

If a post code has a varying number of leading alpha characters, let us
know and we'll try a different solution.

[Stefi]

I think the only way is to transform yor postcodes column with this formula
in a helper column:

=LEFT(A2,2)&TEXT(MID(A2,3,255),"00")

and sort by this new column.

Regards,
Stefi

„jillysillybilly” ezt �*rta:

> I have a list in Excel that I need to sort by postcodes, but in its current
> format, the postcodes (Column A) go like this, eg: BA1 then BA10 BA11 BA12
> etc then BA2, but I want them to go BA1 BA2 BA3 etc. How do I do this?

[Sheila D]

This is exactly the question I came on line to look for an answer to so hope
it's all right to tag on the end of this one - my postcodes have varying
alpha, numeric i.e SE2, SE11, S2 etc and cannot see how to get them to sort
properly as there is no logical way to split alpha / numeric. Thanks for any
help

Sheila

"Dave O" wrote:

> Please excuse my ignorance: are post codes always in the format
> alpha-alpha-number-number-number?
>
> Excel is treating these entries as text because of the BA characters.
> If you separate the alpha characters from the numerics into two
> different columns, you can sort on the different columns to get your
> desired results. So if you enter two new columns, you can parse the
> alpha characters into one column using this formula (assuming that post
> codes always have two leading alpha characters, which was the genesis
> behind my original question):
> =MID(A1,1,2)
> .... where A1 is a post code.
>
> Then get the numeric portion in a new column with this formula:
> =MID(A1,3,LEN(A1))
>
> If a post code has a varying number of leading alpha characters, let us
> know and we'll try a different solution.
>
>

[Roger Govier]

Hi Sheila & Jilly

Make a copy of your data first - just in case!!!

Assuming that you only want to sort by the first part of the UK postcode,
then set up a helper column and use this formula.
This assumes your postcodes are in column A, change reference to suit.

=IF(ISNUMBER(--MID(A1,2,1)),LEFT(A1,1)&TEXT(MID(A1,2,2),"00"),LEFT(A1,2)&TEXT(MID(A1,3,2),"00"))
Copy down the column as far as required, select ALL OF YOUR DATA, and sort
by the helper column ascending.


Regards

Roger Govier


Sheila D wrote:
> This is exactly the question I came on line to look for an answer to so hope
> it's all right to tag on the end of this one - my postcodes have varying
> alpha, numeric i.e SE2, SE11, S2 etc and cannot see how to get them to sort
> properly as there is no logical way to split alpha / numeric. Thanks for any
> help
>
> Sheila
>
> "Dave O" wrote:
>
>
>>Please excuse my ignorance: are post codes always in the format
>>alpha-alpha-number-number-number?
>>
>>Excel is treating these entries as text because of the BA characters.
>>If you separate the alpha characters from the numerics into two
>>different columns, you can sort on the different columns to get your
>>desired results. So if you enter two new columns, you can parse the
>>alpha characters into one column using this formula (assuming that post
>>codes always have two leading alpha characters, which was the genesis
>>behind my original question):
>>=MID(A1,1,2)
>>.... where A1 is a post code.
>>
>>Then get the numeric portion in a new column with this formula:
>>=MID(A1,3,LEN(A1))
>>
>>If a post code has a varying number of leading alpha characters, let us
>>know and we'll try a different solution.
>>
>>

[Gord Dibben]

Dave

Postal codes(Canadian) are always in the

Alpha-Numeric-Alpha-Numeric-Alpha-Numeric form.

V9P2S2 for instance


Gord Dibben Excel MVP

On 8 Dec 2005 06:40:32 -0800, "Dave O" <[email protected]> wrote:

>Please excuse my ignorance: are post codes always in the format
>alpha-alpha-number-number-number?
>
>Excel is treating these entries as text because of the BA characters.
>If you separate the alpha characters from the numerics into two
>different columns, you can sort on the different columns to get your
>desired results. So if you enter two new columns, you can parse the
>alpha characters into one column using this formula (assuming that post
>codes always have two leading alpha characters, which was the genesis
>behind my original question):
>=MID(A1,1,2)
>... where A1 is a post code.
>
>Then get the numeric portion in a new column with this formula:
>=MID(A1,3,LEN(A1))
>
>If a post code has a varying number of leading alpha characters, let us
>know and we'll try a different solution.

[Sheila D]

Now that is VERY clever, thanks a lot Roger

Sheila

"Roger Govier" wrote:

> Hi Sheila & Jilly
>
> Make a copy of your data first - just in case!!!
>
> Assuming that you only want to sort by the first part of the UK postcode,
> then set up a helper column and use this formula.
> This assumes your postcodes are in column A, change reference to suit.
>
> =IF(ISNUMBER(--MID(A1,2,1)),LEFT(A1,1)&TEXT(MID(A1,2,2),"00"),LEFT(A1,2)&TEXT(MID(A1,3,2),"00"))
> Copy down the column as far as required, select ALL OF YOUR DATA, and sort
> by the helper column ascending.
>
>
> Regards
>
> Roger Govier
>
>
> Sheila D wrote:
> > This is exactly the question I came on line to look for an answer to so hope
> > it's all right to tag on the end of this one - my postcodes have varying
> > alpha, numeric i.e SE2, SE11, S2 etc and cannot see how to get them to sort
> > properly as there is no logical way to split alpha / numeric. Thanks for any
> > help
> >
> > Sheila
> >
> > "Dave O" wrote:
> >
> >
> >>Please excuse my ignorance: are post codes always in the format
> >>alpha-alpha-number-number-number?
> >>
> >>Excel is treating these entries as text because of the BA characters.
> >>If you separate the alpha characters from the numerics into two
> >>different columns, you can sort on the different columns to get your
> >>desired results. So if you enter two new columns, you can parse the
> >>alpha characters into one column using this formula (assuming that post
> >>codes always have two leading alpha characters, which was the genesis
> >>behind my original question):
> >>=MID(A1,1,2)
> >>.... where A1 is a post code.
> >>
> >>Then get the numeric portion in a new column with this formula:
> >>=MID(A1,3,LEN(A1))
> >>
> >>If a post code has a varying number of leading alpha characters, let us
> >>know and we'll try a different solution.
> >>
> >>
>

[Roger Govier]

You're very welcome Sheila.
Glad it worked for you.

Regards

Roger Govier


Sheila D wrote:
> Now that is VERY clever, thanks a lot Roger
>
> Sheila
>
> "Roger Govier" wrote:
>
>
>>Hi Sheila & Jilly
>>
>>Make a copy of your data first - just in case!!!
>>
>>Assuming that you only want to sort by the first part of the UK postcode,
>>then set up a helper column and use this formula.
>>This assumes your postcodes are in column A, change reference to suit.
>>
>>=IF(ISNUMBER(--MID(A1,2,1)),LEFT(A1,1)&TEXT(MID(A1,2,2),"00"),LEFT(A1,2)&TEXT(MID(A1,3,2),"00"))
>>Copy down the column as far as required, select ALL OF YOUR DATA, and sort
>>by the helper column ascending.
>>
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>Sheila D wrote:
>>
>>>This is exactly the question I came on line to look for an answer to so hope
>>>it's all right to tag on the end of this one - my postcodes have varying
>>>alpha, numeric i.e SE2, SE11, S2 etc and cannot see how to get them to sort
>>>properly as there is no logical way to split alpha / numeric. Thanks for any
>>>help
>>>
>>>Sheila
>>>
>>>"Dave O" wrote:
>>>
>>>
>>>
>>>>Please excuse my ignorance: are post codes always in the format
>>>>alpha-alpha-number-number-number?
>>>>
>>>>Excel is treating these entries as text because of the BA characters.
>>>>If you separate the alpha characters from the numerics into two
>>>>different columns, you can sort on the different columns to get your
>>>>desired results. So if you enter two new columns, you can parse the
>>>>alpha characters into one column using this formula (assuming that post
>>>>codes always have two leading alpha characters, which was the genesis
>>>>behind my original question):
>>>>=MID(A1,1,2)
>>>>.... where A1 is a post code.
>>>>
>>>>Then get the numeric portion in a new column with this formula:
>>>>=MID(A1,3,LEN(A1))
>>>>
>>>>If a post code has a varying number of leading alpha characters, let us
>>>>know and we'll try a different solution.
>>>>
>>>>
>>

[jillysillybilly]

Thanks Stefi but unfortunately, being a bit of an Excel novice, I have no
idea how to create a helper column, so I was a bit unsure how to precede - i
did try creating a new column A and putting your formula in there, but it
told me I was creating a loop and I didn't really know what was going on! So
i haven't got any further with it yet.

[jillysillybilly]

Thanks Roger but unfortunately, being a bit of an Excel novice, I have no
idea how to create a helper column, so I was a bit unsure how to proceed - i
did try creating a new column A and putting your formula in there, but it
told me I was creating a loop and I didn't really know what was going on! So
i haven't got any further with it yet.

[Roger Govier]

Hi Jilly

The "helper" column is basically, any unused column on your sheet.
Because you inserted a new column at A, then your original column will
now be B, and all my formulae would need to be altered to reference B
rather than A.

Assuming your data is ain column A, and assuming say column M is empty,
then enter the formulae in column M (as the helper column) and proceed
as directed.

If your data is not in column A, but is in say column D, then wherever
you choose to insert the formulae, you would need to amend them to
change all A's to D's in this example.

I hope this makes it all a bit clearer.

--
Regards

Roger Govier


"jillysillybilly" <[email protected]> wrote in
message news:[email protected]...
> Thanks Roger but unfortunately, being a bit of an Excel novice, I have
> no
> idea how to create a helper column, so I was a bit unsure how to
> proceed - i
> did try creating a new column A and putting your formula in there, but
> it
> told me I was creating a loop and I didn't really know what was going
> on! So
> i haven't got any further with it yet.