Function to sort words in a cell almost works

[kanigetts]

I have a custom function to alphabetically sort words within a cell. I didn't write it and can't remember where I got it but it doesn't work perfectly. It will correctly sort comma delimited items except when there are only 2 values. When there are only 2 values it does nothing and leaves them in their original order. I am not a programmer, so I don't know what is going wrong. Here is the code:


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</pre>

 

[mvptomlinson]

Change "If n <= 1 Then" to "If n < 1 Then"

The original says that if the UBound of the array is 1 or less, then the function (StrSort) should simply return the input string (sInp). Well, when the UBound of a zero-based array is 1, there are two elements: 0 and 1. Changing the operand to "<" will return the original string if there is no comma (only one value provided).

 

[devonknows]

How are you calling the Formula/function?

Are you using it through VBA or you using it through excel sheets, if the latter please supply the formula you are using for referene

 

[mvptomlinson]

Hopefully it's similar to:

=strsort("A,D,B,I,C")

or

=strsort(A10,TRUE)

The first will sort the string provided in ascending order. The second would sort the string in cell A10 in descending order, expecting a comma to delimit each value.

 

[Trebor76]

Change this line of the function's code...

If n <= 1 Then

...to this:

If n < 1 Then

HTH

Robert

 

[devonknows]

Change "If n <= 1 Then" to "If n < 1 Then"

The original says that if the UBound of the array is 1 or less, then the function (StrSort) should simply return the input string (sInp). Well, when the UBound of a zero-based array is 1, there are two elements: 0 and 1. Changing the operand to "<" will return the original string if there is no comma (only one value provided).

MVPTomlinson has it all there,
Find

    If n <= 1 Then
        Str Sort = sInp
    Else
        For i = 0 To n - 1

To

    If n < 1 Then
        Str Sort = sInp
    Else
        For i = 0 To n - 1

Regards

 

[kanigetts]

Thanks for the help and explanation. It worked perfectly. Fastest forum response ever! Here is the fixed function:

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