Excel UDF - sort numbers and text separated by comma within a cell

Question

I have a column of data which has numbers and text in each cell, separated by a comma. I found a UDF in another forum (see code below) which sort of does the job but not quite. For example:

Original cell:

84,86,NA,268,277,400,411,42,120,244,346

UDF result:

120, 244, 268, 277, 346, 400, 411, 42, 84, 86, NA

Desired result:

42, 84, 86, 120, 244, 268, 277, 346, 400, 411, NA

I was wondering if someone could help me fix this code. Thank you much. Best Wishes Manoj

See the code for the UDF i found in another forum

Function StrSort(ByVal sInp As String, _
                  Optional bDescending As Boolean = False) As String
    ' sorts a comma-delimited string
    Dim asSS()  As String    ' substring array
    Dim sSS     As String    ' temp string for exchange
    Dim n       As Long
    Dim i       As Long
    Dim j       As Long

    asSS = Split(sInp, ",")
    n = UBound(asSS)

    For i = 0 To n
        asSS(i) = Trim(asSS(i))
    Next

    If n < 1 Then
        StrSort = sInp
    Else
        For i = 0 To n - 1
            For j = i + 1 To n
                If (asSS(j) < asSS(i)) Xor bDescending Then
                    sSS = asSS(i)
                    asSS(i) = asSS(j)
                    asSS(j) = sSS
                End If
            Next j
        Next i
        StrSort = Join(asSS, ", ")
    End If
End Function

CharlesPL CharlesPL · Accepted Answer · 2019-04-02T20:36:40

Your code is treating the contents of your arrays as text, therefore that's why values are sorted like they are.

Unfortunately considering only changing the type of your arrays from String to Long or Double is not as simple, because of your NA (or any other String) value...

This solution is not very elegant and I don't like to see Function that are that long, but it works.

Public Function StrSort(ByVal sInp As String, _
                  Optional bDescending As Boolean = False) As String
    ' sorts a comma-delimited string
    Dim asSS()  As String    ' substring array
    Dim sSS     As String    ' temp string for exchange
    Dim n       As Long
    Dim i       As Long
    Dim j       As Long

    asSS = Split(sInp, ",")
    n = UBound(asSS)

    'First, we are gonna sort Numeric values from every other type of value.
    'The numeric values are going to be stored in an array containing only numeric values
    Dim TemporaryNumberArray() As Double
    For i = 0 To n
        If IsNumeric(Trim(asSS(i))) Then
            On Error Resume Next
            If IsError(UBound(TemporaryNumberArray)) Then
                ReDim TemporaryNumberArray(0 To 0)
            Else
                ReDim Preserve TemporaryNumberArray(0 To UBound(TemporaryNumberArray) + 1)
            End If
            On Error GoTo 0
            TemporaryNumberArray(UBound(TemporaryNumberArray)) = asSS(i)
        End If

    Next


    n = UBound(TemporaryNumberArray)

    'Now, we are going to sort the numbers array.
    If n < 1 Then
        StrSort = sInp
    Else
        For i = 0 To n - 1
            For j = i + 1 To n
                If (TemporaryNumberArray(j) < TemporaryNumberArray(i)) Xor bDescending Then
                    sSS = TemporaryNumberArray(i)
                    TemporaryNumberArray(i) = TemporaryNumberArray(j)
                    TemporaryNumberArray(j) = sSS
                End If
            Next j
        Next i

        'Now, we are building the return string that contains the numbers in order
        StrSort = CStr(TemporaryNumberArray(0))
        For i = 1 To n
            StrSort = StrSort & ", " & CStr(TemporaryNumberArray(i))
        Next
    End If

    'Finally, we are going to append the non-numeric values at the end, in the same order as they appear in the input string
    If n < UBound(asSS) Then
        For i = 0 To UBound(asSS)
            If Not IsNumeric(asSS(i)) Then
                StrSort = StrSort & ", " & asSS(i)
            End If
        Next
    End If
End Function

Excel UDF - sort numbers and text separated by comma within a cell

1 Answers