# Thread: Sort letters in a excel cell (excel xp)

1. ## Sort letters in a excel cell (excel xp)

I have a number of cells in a range and I want to sort the contents of each cell in alphabetical order (A-Z)

e.g
a1 = dgtke
a2 = fjers

should become
a1=degkt
a2=efjrs

gathering the contents of the cells is no problem (each cell in range)
Don't know how to do the sort (do I convert to chr?)
Is it better to load into an array?

Thanks

Simon

2. ## Re: Sort letters in a excel cell (excel xp)

If you select the cells and then run the VBA code below it should do what you asked.

<pre>Public Sub SortContents()
Dim oCell As Range
Dim I As Integer, J As Integer, iLen As Integer
Dim strChr() As String, strWk As String
For Each oCell In Selection
strWk = oCell.Value
iLen = Len(strWk)
ReDim strChr(1 To iLen)
For I = 1 To iLen
strChr(I) = Left(strWk, 1)
strWk = Right(strWk, Len(strWk) - 1)
Next I
For I = 1 To iLen - 1
For J = I + 1 To iLen
If strChr(J) < strChr(I) Then
strWk = strChr(I)
strChr(I) = strChr(J)
strChr(J) = strWk
End If
Next J
Next I
strWk = ""
For I = 1 To iLen
strWk = strWk & strChr(I)
Next I
oCell.Value = strWk
Next oCell
End Sub
</pre>

3. ## Re: Sort letters in a excel cell (excel xp)

Excellent, how does it work??

Simon

4. ## Re: Sort letters in a excel cell (excel xp)

For each cell, an array strChr is filled with the individual characters of the cell text. This array is sorted in alphabetical order by the Bubble Sort method; this method starts by taking the first array element and successively comparing it to the second, third etc. element; if the latter precedes the first alphabetically, they are swapped. At the end of this, the first array element is OK for the sort order. Next, the second array element is compared to the third, fourth etc. element, swapped if necessary, and so on. Finally, the array elements (now in alphabetical order) are concatenated into a string and this is assigned to the cell. The strWk variable is used cleverly as a temporary variable throughout the routine.

Bubble Sort is the simplest method of sorting; it works fine for sorting relatively short arrays. For large size arrays, it is very inefficient, because execution time increases with the square of the array length. Sophisticated methods such as QuickSort are much faster (if you are interested, do a Google for QuickSort and you'll find loads of information). In this case, such methods are probably overkill.

5. ## Re: Sort letters in a excel cell (excel xp)

Thanks Hans, I could not have said it better. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

#### Posting Permissions

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