# Sort Contents Within 1 Cell

#### Nanogirl21

##### Active Member
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

<tbody>
</tbody>

WANTED RESULTS

 1 APPLES BANANA CHERRY GRAPES WATERMELON 2 BLUE GREEN PURPLE YELLOW 3 APRIL AUGUST JULY JUNE DECEMBER

<tbody>
</tbody>

Last edited:

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### CalcSux78

##### Well-known Member
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``````

#### JMeendering

##### New Member
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``````

Replies
9
Views
521
Replies
8
Views
311
Replies
6
Views
449
Replies
10
Views
455
Replies
3
Views
395

1,109,397
Messages
5,528,495
Members
409,820
Latest member
gabrielrms

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...