Sorting a list of UK Postal codes in Excel 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mikey29 - 24 Apr 2007 12:55 GMT

Hi, I am relatively new to Excel and am using the 2003 version. I am
currently learning about lists. I have compiled a list of personal
data with names and addresses. I have put the Postal Codes in a
separate column.
When I sort the list in ascending order by Postal code I get e.g the
following:

SA1
SA11
SA12
SA5
SA6
SA8

Can anyone please advise how I can sort SA5,SA6 and SA7 to come before
SA11 and SA12 in the list.
Many thanks

Reply to this Message

Bob Flanagan - 24 Apr 2007 13:24 GMT

Add another column and use a formula like this to convert the 3 digit
entries to 4 digit entries. Then sort on the new column.

= if(len(A1) = 3, left(A1,2) & "0" & right(A1,1), A1)

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

> Hi, I am relatively new to Excel and am using the 2003 version. I am
> currently learning about lists. I have compiled a list of personal
[quoted text clipped - 13 lines]
> SA11 and SA12 in the list.
> Many thanks

Reply to this Message

David McRitchie - 25 Apr 2007 03:35 GMT

Hi Mikey,
The number of letters can vary and the number of
digits can vary. I think the following will work for you
Sorting, Some notes on sorting in Excel
http://www.mvps.org/dmcritchie/excel/sorting.htm#pcdigits

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> Add another column and use a formula like this to convert the 3 digit
> entries to 4 digit entries. Then sort on the new column.
[quoted text clipped - 23 lines]
> > SA11 and SA12 in the list.
> > Many thanks

Reply to this Message

Rate this thread: