Sorting Mixed Text and Numbers

I need help with a sorting problem. A column holds numbers and numbers with suffixes. The conventional sort function pushes the text values to the bottom. I need them sorted along with the numbers. Simple? Maybe not.

Microsoft's KB says to add a column formatted as text and then to RETYPE ALL OF THE VALUES! Splendid. But my file has hundreds of records. Re-typing all of them would be a major pain. On the other hand, if I had started typing while trying to find a way to sort the darn thing I might be done by now. ;-)

Here's what I know so far: If we create a column, format it as text and then populate with 1, 2, 3, 4, 1a, 2a, 3a, 4a, etc. it will sort exactly correct (1, 1a, 2, 2a...) after selecting the "sort numbers and numbers stored as text separately" when the Sort Warning appears. If you try to create the sample I typed here, you need to be careful that the area is formatted as text BEFORE entering the text. You apparently cannot change the format of an existing column of data to Text or if you do it does not have the desired effect when sorting. It might be helpful to know if this can be done too. MS suggests not.

Can anybody come up with a solution to properly sort this column of data? And why is this problem causing me a daja vu feeling?

Thanks for your suggestions.

do all labels contain at most a 3digit number followed by a 2 letter/number?

Here's a vba solution if you like.

yes, no more than three digits.

Hi jindon,

Your VBA solution works like a charm. Thanks so very much. I am somewhere between novice and intermediate in VBA but your code just lost me completely. Would you be so kind as to tell me how it works and what it is doing? Is it custom made for my exact needs or can it be applied in general to sort problems of mixed text and numbers? (See, I can't even figure that out from looking at your code! Duh.)

For the benefit of others, I will reproduce Jindon's code here. Again, it works like a charm; I hope others will find this helpful too.

Sub test()
    Dim a, i As Long
    With Cells(1).CurrentRegion
        a = .Value
        ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
        With CreateObject("VBScript.RegExp")
            .Pattern = "\d+(\.\d+)?"
            For i = 2 To UBound(a, 1)
                If .test(a(i, 1)) Then a(i, UBound(a, 2)) = _
                Format$(.Execute(a(i, 1))(0), String(20, "0")) & a(i, 1)
            Next
        End With
        VSortM a, 2, UBound(a, 1), UBound(a, 2), 1
        .Value = a
    End With
End Sub

Private Sub VSortM(ary, LB, UB, ref, Optional ord As Boolean = 1)
    Dim M As Variant, i As Long, ii As Long, iii As Long, temp
    i = UB: ii = LB
    M = ary(Int((LB + UB) / 2), ref)
    Do While ii <= i
        If ord Then
            Do While ary(ii, ref) < M: ii = ii + 1: Loop
        Else
            Do While ary(ii, ref) > M: ii = ii + 1: Loop
        End If
        If ord Then
            Do While ary(i, ref) > M: i = i - 1: Loop
        Else
            Do While ary(i, ref) < M: i = i - 1: Loop
        End If
        If ii <= i Then
            For iii = LBound(ary, 2) To UBound(ary, 2)
                temp = ary(ii, iii): ary(ii, iii) = ary(i, iii): ary(i, iii) = temp
            Next
            ii = ii + 1: i = i - 1
         End If
    Loop
    If LB < i Then VSortM ary, LB, i, ref, ord
    If ii < UB Then VSortM ary, ii, UB, ref, ord
End Sub

1) Store the data in array, variable a.
2) Resize the array 1) by one column at the end to use it as a key column.
3) Loop through the array to get the first appear number(s) if there is.
4) If the number(s) exists, convert it as 20 digits number as a string so that it can be sorted propery.
5) then add actual value at the end of 4) to make the sort within the same numbers,(duplicates)
6) Use "QuickSort" algorithm in "VSortM" sub procedure to sort the array.
If you change the last argument to 0, it will sort in descending order.

The main concept is to convert the data like

12xxx ----> 0000000000000000001212xxx
xyz002yy ----> 0000000000000000002xyz002yy

so that it will be sorted properly.

However, if you have data like

123xxx2 -----> 00000000000000000123123xxx2
123xxx10 ----> 00000000000000000123123xxx10

123xxx10 comes before 123xxx2
You see the reason in above, so it needs to adjust it further.

To shg:

Thanks for your simple solution. It ALMOST works, but it sorted 1, 10, 100 above 2. Any thoughts on how to fix that? Or did I miss something? Either way, thanks again for your input!

To jindon:

Thanks for your explanation. This is most helpful. I am very (albeit easily) impressed! I really appreciate your help!

Thanks shg; I will give it a try. I really appreciate your effort.

If you are still active I would love some help with this, I have the same problem and I cannot get this code to work yet.

rwat1,

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

@jheiler1

You can check out my thread in which Jindon responded. https://www.excelforum.com/excel-pro...and-paste.html