Developing and automating a custom alphanumeric sort in Excel

Posted on 2014-07-25
Last Modified: 2014-07-28
I need to be able to develop and automate a custom sort on an alphanumeric list. We will be sorting this type of data on a regular basis, hence the most automated approach would be best. I have attached a sample excel showing the Excel default sort in column A, and the correct custom sort I need in column B.

Question by:Andreamary
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 27

Expert Comment

ID: 40219633
Correct me if my observation is wrong. This is the sort you need:
1. The data should be separated into subsets for each number of characters per word
2.  Each subset should be sorted by itself.
3. Once the data is separated into subsets it actually is sorted already.
LVL 33

Expert Comment

by:Rob Henson
ID: 40219697
If MacroShadow is correct, to achieve that sort use a helper cell alongside the data with the following formula:


Sort on the column contianing this formula and then on column A.

Rob H

Author Comment

ID: 40219778
I tried the above formula but it wasn't entirely successful...I've attached my results and highlighted the errors in red. They pertain to the "N" section, with those identified as "4's" in the helper column. To answer Macroshadow to the best of my abilities:

First sort — alpha, on first letter in string
Second sort — numeric, on all numbers within string, treating the number sequence within the string as a single number (it can be 1, 2, 3+ digits as per the example).
Third sort — alpha, on last letter in string

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 27

Expert Comment

by:Glenn Ray
ID: 40219827
No points..just clarifying how you should sort using Rob's helper column:
sort order
LVL 27

Expert Comment

by:Glenn Ray
ID: 40219870
Followup:  Where would the value "CUT16" be ordered?  It does not have a single letter to start, but based on your rules above, I presume it should be at the top of the list.  Are there any other designations like this that might need to be considered?

Author Comment

ID: 40219907
Hi Glenn,
Thanks for noting the CUT exception...I'm not very familiar with the data (troubleshooting for a colleague) but you're right, CUT should be at the top of the list. And confirming that I did sort as per your example with the helper column.

LVL 27

Expert Comment

by:Glenn Ray
ID: 40219946
So, given that issue, would it be correct to re-state the sorting algorithm like so?

1) Sort on all alphabetic characters at the start of each string, ascending
2) Sort on all numeric characters that follow, ascending
3) Sort on any alphabetic characters at the end of the string (if they exist), ascending

Author Comment

ID: 40219964
Yes, a formula (or VBA) that does that should do the trick...

LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40220323
As I was writing a UDF (user-defined function) that would help supply a new sorting string, I noticed that there were no instances where there was more than one letter suffix for any given set of letter-number combinations.  For example:


Put another way, instead of the designations being constructed like this:
AlphaCode1 - NumericCode (- AlphaCode2)   <---last value optional
it's more like:
AlphaCode1 - AlphanumericCode


meaning that the alphanumeric group is mutually-exclusive.

Long story short, I wanted to make sure that this would work if the first case was true, so I added some extra test values (in green) to ensure the UDF would work.  The attached workbook contains this function:
Function SortString(strInput As String)
    Dim x As Integer
    Dim strNum As String
    Dim boolBreak As Boolean
    For x = 1 To Len(strInput)
        If IsNumeric(Mid(strInput, x, 1)) Then
            boolBreak = True
                strNum = strNum & Mid(strInput, x, 1)
                x = x + 1
            Loop Until IsNumeric(Mid(strInput, x, 1)) = False
            SortString = SortString & Format(Val(strNum), "000000")
            SortString = SortString & Mid(strInput, x, 1)
        End If
    Next x
End Function

Open in new window

You would sort on the helper column with the function (col. B), then the Designation column to ensure that extra letter combinations are picked up correctly.


Author Comment

ID: 40222676
Hi Glenn,

Thanks very much. I've tested this out, and it appears that when I sort by Column B (SortString), then by column A, there are still issues with the numbers being out of order (3-digit numbers preceding 2-digit numbers as shown below). It appears, though, if I change the order in which I sort the columns, sorting Column A first, then Column B, it appears to work. Is there a reason it would work this way, and would it always work? I've attached my test SortString file.


LVL 27

Expert Comment

by:Glenn Ray
ID: 40223453
When you do the sort, it should show the column names and you should select as follows:
correct sorting order
This is the only way it should work.


Author Closing Comment

ID: 40224384
It works perfectly, Glenn...much appreciated! (Thanks for your patience in steering me in the right direction..I was manually sorting each column instead of using the SORT interface as shown in your screen capture.)
LVL 27

Expert Comment

by:Glenn Ray
ID: 40224719
You're welcome.  Wish I could have figured an "elegant" helper formula instead of a UDF, but glad that it works.


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question