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

1. ## 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

2. ## 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)
Next i
coll.Sort
aTemp = coll.ToArray
cel.Value = Join(aTemp, Chr(10))
Erase Arr
Erase aTemp
coll.Clear
Next
End Sub```

3. ## 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```