Sorting alphanumeric text

Topic

New Reply

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

Reply | Quote

Replies

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

 

Reply | Quote

Sorry, here is my spreadsheet.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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

Reply | Quote

Hanan wrote:

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

Reply | Quote

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

 

Reply | Quote

Hi Zeddy,

This is exactly what I need.

Thanks for you assistance. Cheers!

Hanan.

Reply | Quote