+ Reply to Thread
Results 1 to 3 of 3

Sort postcode list alphabeically and numerically

  1. #1
    Registered User
    Join Date
    10-28-2010
    Location
    UK, Swansea
    MS-Off Ver
    Excel 2010
    Posts
    64

    Sort postcode list alphabeically and numerically

    Can anyone please help and advise how can I sort the attached list both alphabetically and numerically. Have failed in my attempts and now need assistance.
    I have a list of UK postcode sectors which I am trying to sort alphabetically and numerically, the order should be eg AB1 0, AB10 1, G2 1, G20 1 but excel wont allow that sort order.
    There are three postcode formats
    XX11 1
    X1 1
    X11 1
    where X an Y could be any letters in the alphabet and 1 could be any digit 0 to 9.
    Have attached a short list and would be grateful if someone could help with a formula in column B which would allow me to sort the list in column A which I can
    then apply to my full list.
    Thanks
    MikeyM
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sort postcode list alphabeically and numerically

    try
    =LEFT(A3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A3&1234567890))-1)&TEXT(MID(A3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A3&1234567890)),2)+0,"00")&" "&RIGHT(A3)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    10-28-2010
    Location
    UK, Swansea
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Sort postcode list alphabeically and numerically

    Brilliant, many thanks, saves me lot of manual work!!.
    Rgds
    Mike

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. 2 columns, can I sort them numerically?
    By Gol in forum Excel General
    Replies: 2
    Last Post: 08-05-2009, 08:10 PM
  2. sort alphabetically and numerically, then sort rows
    By luke20allen in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-01-2008, 07:00 AM
  3. I need to sort alphabetically and then numerically
    By luke20allen in forum Excel General
    Replies: 3
    Last Post: 09-18-2008, 10:22 PM
  4. how do i sort a column numerically going from 01-01 to 225-99
    By column sorting in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2005, 06:20 PM
  5. how do i sort a column numerically going from 01-01 to 225-99
    By column sorting in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1