Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate contents of cells whose contents resemble cell referem | Excel Worksheet Functions | |||
sorting contents from right to left | Excel Worksheet Functions | |||
Sorting on partial Cell contents VBA | Excel Discussion (Misc queries) | |||
Sorting and having cell formats stick with contents | Excel Discussion (Misc queries) | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) |