+ Reply to Thread
Results 1 to 7 of 7

Alphanumeric sort stopped working properly.

  1. #1
    Registered User
    Join Date
    01-26-2015
    Location
    Portland
    MS-Off Ver
    2007
    Posts
    4

    Unhappy Alphanumeric sort stopped working properly.

    Bit of a weird issue here.

    I use a lot of alphanumeric data (not by choice) and need to sort them properly. It used to work flawlessly with the tips described on Microsoft support pages, here : http://support.microsoft.com/kb/322067. I used the TEXT(A1,"###") function.

    Now, it will not work for reasons unknown.

    Basically, I would need this :

    2a
    45b
    6
    3c
    4
    7a
    11
    46
    43

    To look like this :

    2a
    3c
    4
    6
    7a
    11
    43
    45b
    46

    But Excel sorts it like this :

    4
    6
    11
    43
    46
    2a
    3c
    7a
    45b

    There were no updates on our servers, and I use the same Excel version (2007) as before, so I'm really stumped. I tried it at home just for fun and it worked fine.

    Anyone has an idea why the alphanumeric sort would suddenly not work ?

    Thanks.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Alphanumeric sort stopped working properly.

    Is it able to use helper column to sort?
    Quang PT

  3. #3
    Registered User
    Join Date
    01-26-2015
    Location
    Portland
    MS-Off Ver
    2007
    Posts
    4

    Re: Alphanumeric sort stopped working properly.

    I create a helper column, yes.

    I populate it with the function TEXT(A1,"###") [so I basically make a text-only copy of my data range], then I sort the whole sheet based on the helper column.

    That used to do the trick. It doesn't anymore.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Alphanumeric sort stopped working properly.

    Try in B1 to get numeric value:

    =MAX(IFERROR(MID(A1,1,{1,2,3,4,5})+0,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Drag down then sort column B for whole sheet

  5. #5
    Registered User
    Join Date
    01-26-2015
    Location
    Portland
    MS-Off Ver
    2007
    Posts
    4

    Re: Alphanumeric sort stopped working properly.

    Well...it looked a bit strange, but it worked !

    It's not the very best solution because it merely works around the original problem, but I'm certainly not complaining.

    If you use this solution, make sure to press CTRL + SHIFT + ENTER as stated above, and keep in mind that the letters will not show in your helper column, only numbers. Ignore that and follow through with the sort, making sure you sort based on your helper column. Your sheet will now be properly sorted in alphanumeric order.

    Cheers.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Alphanumeric sort stopped working properly.

    Is it the case 2a and 2b? my formula ignores this.

  7. #7
    Registered User
    Join Date
    01-26-2015
    Location
    Portland
    MS-Off Ver
    2007
    Posts
    4

    Re: Alphanumeric sort stopped working properly.

    At first, I thought it did not work, because it did not show the letters.

    I'm just clarifying for other readers that the formula indeed works, despite the apparent confusion.

+ 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. [SOLVED] Excel has stopped working... after custom sort
    By jhren in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-28-2014, 04:46 AM
  2. Alphanumeric sort - Excel 2010 not working
    By nivivani in forum Excel General
    Replies: 7
    Last Post: 07-26-2013, 10:43 AM
  3. Date Sort in Pivot Table - Not working properly
    By claudiocustodio in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-12-2013, 04:30 PM
  4. [SOLVED] Sort function not working properly...
    By dllivermore in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-01-2012, 05:45 PM
  5. Excel 2003 Sort Buttons Not Working Properly
    By crazy1bryan in forum Excel General
    Replies: 2
    Last Post: 08-07-2009, 01:31 PM

Tags for this Thread

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