News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • Sorting alphanumeric text

    Viewing 7 reply threads
    • Author
      Posts
      • #2337246
        Hanan
        AskWoody Lounger

        Hi,

        I have a list of part names in alphanumeric format that I need to sort. I managed to find an equation that sorts out the data, as shown in the attached spreadsheet.

        The spreadsheet contains a subset of the names that I need to sort. Original data stream contains hundreds of lines. The problem is that the sorting operation does not sort the names in the order that I am looking for.

        For instance, the sort operation produces:
        A1, A10, …., A2
        Instead of:
        A1, A2, …, A10

        I did some research and found that the Len(A1) function can be used as a secondary sort operation. However, I could not successfully integrate the LEN function into the above equation.

        I would prefer to keep the sort operation in one equation. However, if it is not possible, I would not object to using a helper cell. In this case, I would like to ask if an example using the attached spreadsheet can be used to show me how to make it work.

        PS: I am using Excel version 2013

        Thank you,

        Hanan

        Sorting_of_alphanumeric_part_names

      • #2337318
        Paul T
        AskWoody MVP

        Have you tried the method shown here: https://www.extendoffice.com/documents/excel/2730-excel-sort-data-alphanumeric.html

        cheers, Paul

        p.s. the spreadsheet isn’t attached

         

      • #2337666
        Hanan
        AskWoody Lounger

        Sorry, here is my spreadsheet.

        Attachments:
      • #2337671
        Hanan
        AskWoody Lounger

        I would prefer to use an equation to sort out my data.  The equation I use is:

        =IFERROR(INDEX($A$3:$A$22,MOD(AGGREGATE(15,6,(COUNTIF($A$3:$A$22,”<“&$A$3:$A$22)*10000+(ROW($A$3:$A$22)-ROW($A$2)))/($A$3:$A$22<>””),ROWS($A$3:$A3)),10000)),””)

        However, the sort operation yields:
        A1, A10, …., A2

        Instead of:
        A1, A2, …, A10

        Can I modify the above equation to sort in in the order that I listed?

        Thanks.

      • #2337750
        bratkinson
        AskWoody Plus

        I’ve run into that problem countless times both when sorting in a spreadsheet or even simply sorting the names of the months into sequential order.  I’ve even played games to force lists of file names into a non-standard, but logical to me sequence.

        For data outputs of an app, simply force it to use 2 or 3 digit numbers, unconditionally prefixing with zeroes as needed.  Other times, I create file names like 01-January, 02-February, etc to sort them into order.

        I’ve also done semi-manual sequencing with a combination of 2 or 3 digit numbers with optional suffixes, effectively creating a second sort key.  For example, given a list of book titles and subjects, to sort by subject, I’d start with making any subject starting with the letter ‘A’ be 01, ‘B’ 02, and then serially using letters (or numbers) to sub sort such that ’15’ might be a subject Axxxx Bxxxx and ‘rough sort’ them into sequence creating 0115.  Think of it as a card index system where all the ‘A’s are together, then within the ‘A’s, everything is alphabetical or nearly so.  For file names, it requires a lot of manually renaming files in Windows, which is rather clunky, to say the least.  However…I downloaded a utility to produce any kind of mass renaming desired including fixed pre/suffixes as well as sequentially ascending/descending numbers with a fixed number of digits, etc.  Discovering that shareware program some years back greatly simplified renaming selections from groups of my photographs (various shoots) to increment by 1 so I readily can distribute them on CDs or electronically without the recipients seeing small and large gaps of image numbers.

        • #2337825
          anonymous
          Guest

          bratkinson wrote:
          I’ve even played games to force lists of file names into a non-standard, but logical to me sequence.

          For data outputs of an app, simply force it to use 2 or 3 digit numbers, unconditionally prefixing with zeroes as needed. Other times, I create file names like 01-January, 02-February, etc to sort them into order.

          Yes, use of sensible naming conventions/data formats can help bypass annoying sort issues. Probably the most common “sort aware” format I’ve used over the years is the classic YYYYMMDD date format (e.g., 26 Jan 2021 => 20210126).

          • #2343253
            zeddy
            AskWoody_MVP

            I meant to reply to this yesterday (12th Feb 2021), when we had

            YYYYMMDD = DDMMYYYY

            i.e. palindromic date in UK where 12-02-2021 is same backwards…

            yddez

      • #2337787
        Paul T
        AskWoody MVP

        I would prefer to use an equation to sort out my data

        Your equation needs to return text, not numbers, for sort to work. See the post linked above for details.

        cheers, Paul

      • #2338566
        zeddy
        AskWoody_MVP

        Hi Hanan

        You can use your formula (with a slight modification) to sort your data, but would need a ‘helper column’ to get your Parts List into a ‘standardised format’.

        Your current sample data shows Parts list with a format of 1 or 2 chars, followed by 1 to 3 digits.

        Just in case, lets assume it could actually be up to 4-chars, then followed by up to 6-digits
        We could adjust this as required.

        We would use a hidden helper column, containing a formula to ‘convert’ the Part ID in column A into a standardised-length-format of say 10

        i.e. 4-chars followed by 6-digits e.g. aaaa000000

        We then use your formula to fetch the actual Part ID in column A, but using our helper column B range in your formula.

        see attached file

        zeddy

        Sorting_of_alphanumeric_part_names-zeddy-1

         

        Attachments:
      • #2339006
        Hanan
        AskWoody Lounger

        Hi Zeddy,

        This is exactly what I need.

        Thanks for you assistance. Cheers!

        Hanan.

    Viewing 7 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: Sorting alphanumeric text

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.