Results 1 to 3 of 3

Thread: Sort Contents Within 1 Cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2013
    Posts
    242
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Sort Contents Within 1 Cell

    I have a list of values that is seperated by a return. How do I sort each cell in alphabetical order without moving the row?

    EXAMPLE.
    CURRENT DATA
    1 APPLES
    GRAPES
    WATERMELON
    BANANA
    CHEERY
    2 PINK
    BLUE
    GREEN
    YELLOW
    3 APRIL
    AUGUST
    JULY
    DECEMBER
    JUNE

    WANTED RESULTS

    1 APPLES
    BANANA
    CHERRY
    GRAPES
    WATERMELON
    2 BLUE
    GREEN
    PURPLE
    YELLOW
    3 APRIL
    AUGUST
    JULY
    JUNE
    DECEMBER
    Last edited by Nanogirl21; Jun 5th, 2019 at 03:30 PM.

  2. #2
    Board Regular CalcSux78's Avatar
    Join Date
    Oct 2013
    Location
    STL
    Posts
    1,120
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort Contents Within 1 Cell

    Not sure if there's a worksheet function to do this...

    With this method, you would select a range to have sorted, then run the code.

    Code:
    Sub AlphaSelection()  'select range to sort then run
    
    Dim cel As Range, txt$
    Dim Arr As Variant, i&, aTemp As Variant
    Dim coll As Object
    Set coll = CreateObject("System.Collections.ArrayList")
    
    
    For Each cel In Selection.Cells
        Arr = Split(cel.Value, Chr(10))
        For i = LBound(Arr) To UBound(Arr)
            coll.Add CStr(Arr(i))
        Next i
        coll.Sort
        aTemp = coll.ToArray
        cel.Value = Join(aTemp, Chr(10))
        Erase Arr
        Erase aTemp
        coll.Clear
    Next
    End Sub
    Cunningham's Law: "The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."

  3. #3
    New Member
    Join Date
    Feb 2017
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort Contents Within 1 Cell

    I can not think of a way to do do that without VBA.
    Select the cells that you want and run the macro.
    The delimiter of the carriage return is hardcoded in. CHR(10)

    Code:
    Public Sub SortVals()    Dim i, a, b As Integer
        Dim arr As Variant
        Dim delimiter As String
        Dim cel As Range
        Dim selectedRange As Range
        Set selectedRange = Application.Selection
    'loop through the selected cells
        For Each cel In selectedRange.Cells
            If cel.Value = vbNullString Then GoTo nextCell
            arr = Split(cel, Chr(10))
            ' 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
            delimiter = ""
            cel.Value = ""
            For b = LBound(arr) To UBound(arr)
                cel.Value = cel.Value & delimiter & CStr(arr(b))
                delimiter = Chr(10)
            Next b
    nextCell:
        Next cel
    
    
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •