excel: how do i sort within a cell?

i have a string se开发者_Go百科parated by commas, is it possible to use an excel formula to sort within the values within the cell?

Here's a solution (quicksort code stolen from here). You would just wire up a button to the SortVals macro and you could just click the button and it'll sort the comma separated values in the active cell.

Option Explicit

Public Sub SortVals()
    Dim i As Integer
    Dim arr As Variant
    arr = Split(ActiveCell.Text, ",")

    ' trim values so sort will work properly
    For i = LBound(arr) To UBound(arr)
        arr(i) = Trim(arr(i))
    Next i

    ' sort
    QuickSort arr, LBound(arr), UBound(arr)

    ' load sorted values back to cell
    Dim comma As String
    comma = ""
    ActiveCell = ""
    For i = LBound(arr) To UBound(arr)
        ActiveCell = ActiveCell & comma & CStr(arr(i))
        comma = ","
    Next i
End Sub

Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)

  Dim pivot   As Variant
  Dim tmpSwap As Variant
  Dim tmpLow  As Long
  Dim tmpHi   As Long

  tmpLow = inLow
  tmpHi = inHi

  pivot = vArray((inLow + inHi) \ 2)

  While (tmpLow <= tmpHi)

     While (vArray(tmpLow) < pivot And tmpLow < inHi)
        tmpLow = tmpLow + 1
     Wend

     While (pivot < vArray(tmpHi) And tmpHi > inLow)
        tmpHi = tmpHi - 1
     Wend

     If (tmpLow <= tmpHi) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHi)
        vArray(tmpHi) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHi = tmpHi - 1
     End If

  Wend

  If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
  If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

End Sub

You'd have to split the string, sort the values and create a new string out of the sorted values.

At the time this question was asked my LAselect framework add-in wasn't available to the general public. But now it is and it solves just these kind of problems in an elegant way.

You only have to provide a few lines of 'solution' code in VBA to sort or order anything you want.

To get at the comma-delimited values use VBA's Split() function. You'll then have as many separate values as your cell had; if splitvalues(1) is firstname and splitvalues(0) is lastname, the Add-in could sort your single cell something like this:

Dim splitvalues1 as Variant, splitvalues2 as Variant

While LAselect(ControlSignal, BreakOutBox)
      valcol = BreakOutBox.Pins.ColData

      splitvalues1 = Split(BreakOutBox.Data.VBArray(BreakOutBox.Pins.RowSource, valcol), ",")

      splitvalues2 = Split(BreakOutBox.Data.VBArray(BreakOutBox.Pins.RowDestin, valcol), ",")


      If splitvalues1(0) > splitvalues2(0) Then
         ControlSignal = LA_ISLARGER
      ElseIf splitvalues1(0) < splitvalues2(0) Then
         ControlSignal = LA_ISSMALLER
      Else
         ControlSignal = LA_ISEQUAL
      End If
Wend

At LA_ISEQUAL you would have to test for splitvalues1(1) etc. to further sort duplicate lastnames on first names.

Did it become clear to you that it really does NOT matter for my add-in how many delimter-separated fields you squeeze into a cell or even what other cells you to include into the decision? Both complete records are passed to your 'solution' code and you'll have full freedom what ever property you wish to sort on.

My demo is available from www.liquorice-allsorts.com so you could try this out.