Sort values separated by slash

[YasserKhalil]

Hello everyone

I have the following values in 1d array:

Untitled.png

You can read these items from right to left, I mean 5 / 1 is read as one slash five. When used bubble sort method to sort this 1d array, I got the values sorted but not as expected

Code: Select all

Sub BubbleSort(myArray() As Variant)
    Dim temp, i As Long, j As Long
    For i = LBound(myArray) To UBound(myArray) - 1
        For j = i + 1 To UBound(myArray)
            If myArray(i) > myArray(j) Then
                temp = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = temp
            End If
        Next j
    Next i
End Sub

If I put the values in one column in excel and use the original sort within excel, excel asked me to choose how to deal with numbers and I select the first option (sort anything that looks like numbers, as a number) and this option gives me the exact output. How can I modify the udf to be able to do the same as for sorting the values

Untitled.png

[HansV]

I haven't tested it, but does it work if you change

Code: Select all

            If myArray(i) > myArray(j) Then

to

Code: Select all

            If CDate(myArray(i)) > CDate(myArray(j)) Then

[SpeakEasy]

So what Excel does is it examines the string, and tries to figure out what sort of number it is,. In this case, perhaps surprisingly, it thinks it has dates , and is sorting on that. Now, if that is the output you need, then you just need to change your bubble sort a bit ...

If cdate(myArray(i)) > cdate(myArray(j))Then

This should give you the same order as you show in your second image

Be careful, though, CDATE is not quite what happens on the worksheet itself - works well enough for this example, but you'll get a mismatch if you have say "1/14" in one of the cells. Of course if you really want it to emulate the worksheet - why not stick the array into a temporary worksheet. sort that, and return the result into an array?

[SpeakEasy]

Ah, I see Hans beat me to it

[YasserKhalil]

Thank you very much. CDate does the trick.