Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort letters in a excel cell (excel xp)

    Excellent, how does it work??

    Simon

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Legare Coleman

Posting Permissions

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