2

I'm wondering if there is a way to sort values that are separated by a comma within a cell in microsoft excel. For instance, I have a cell that reads "49, 11" and I'd like it to read "11, 49". Any ideas?

1
  • You'll need VBA to do this without changing the structure of the data. Jan 22, 2014 at 13:46

2 Answers 2

3

Consider the following User Defined Function :

Public Function CellSort(r As Range) As String
    Dim bry() As Long, L As Long, U As Long
    ch = r(1).Text
    ary = Split(ch, ",")
    L = LBound(ary)
    U = UBound(ary)
    ReDim bry(L To U)
    For i = LBound(ary) To UBound(ary)
        bry(i) = CLng(ary(i))
    Next i

    Call BubbleSort(bry)

    For i = LBound(bry) To UBound(bry)
        ary(i) = CStr(bry(i))
    Next i
    CellSort = Join(ary, ",")
End Function


Sub BubbleSort(arr)
    Dim strTemp As Variant
    Dim i As Long
    Dim j As Long
    Dim lngMin As Long
    Dim lngMax As Long
    lngMin = LBound(arr)
    lngMax = UBound(arr)
    For i = lngMin To lngMax - 1
        For j = i + 1 To lngMax
            If arr(i) > arr(j) Then
                strTemp = arr(i)
                arr(i) = arr(j)
                arr(j) = strTemp
            End If
        Next j
    Next i
End Sub
1

Excel sorting feature operate on entire cells. To sort within a cell you'd need to use a VB script. You might consider splitting the cells' contents into columns using Text To Columns and then sorting (left to right).

1
  • Sure a text to columns, transpose and sort would work very well. Jan 22, 2014 at 13:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .