Sorting the contents of a cell
 

I have a range of cells. Each cell can contain multiple
entries separated by a comma and a space.

Example: cell A2 contains: John, Adam, Charlie

How can I sort the contens of the cell so I get:

A2 = Adam, Charlie, John

Thanks,

Maggie B.

Sorting the contents of a cell
 

You can use the split command to convert the content to an array, then sort
the array, use the join command to recreate, then place it back in the cell.

Sub SortCell()
Dim i as Long, j as Long
Dim swap1, swap2
Dim varr
varr = Split(ActiveCell, ",")
' peform bubble sort
For i = 0 To UBound(varr) - 1
For j = i + 1 To UBound(varr)
varr(i) = Trim(varr(i))
varr(j) = Trim(varr(j))
If varr(i) varr(j) Then
Swap1 = varr(i)
Swap2 = varr(j)
varr(j) = Swap1
varr(i) = Swap2
End If
Next j
Next i
ActiveCell.Value = Application.Trim( _
Join(varr, ", "))
End Sub

Assumes Excel 2000 or later.

--
Regards,
Tom Ogilvy


"Maggie B." wrote in message
...
I have a range of cells. Each cell can contain multiple
entries separated by a comma and a space.

Example: cell A2 contains: John, Adam, Charlie

How can I sort the contens of the cell so I get:

A2 = Adam, Charlie, John

Thanks,

Maggie B.