CAN YOU ALPHA SORT IN A SINGLE CELL?

  • Thread starter Thread starter Guest
  • Start date Start date
In cell A1 I have "cbdgeaf". In cells C1:C7, I enter this array
formula:

=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

In cell E1, I have the following formula:

=CONCATENATE(C1,C2,C3,C4,C5,C6,C7)

Select column C. Copy and paste special values. Sort column C, no
headers, ascending, and the result in E1 will be sorted.

Not a great solution, and will only work for single-character
strings. Of course you could make a ridiculous long array formula
that will split up by a delimited, but you still have to go through
the process of paste special values, resort, etc etc. You'll probably
run into function nesting limitations if you're using Excel 2003 or
earlier, formula length problems due to repeated statements for error
checking... in short you don't want to go there.

Another possibility would be using Text-to-columns, then copy/paste
special transpose, then sort. And, like Gary said, VBA is an option
as well.
 
CAN YOU ALPHA SORT IN A SINGLE CELL?
Click to expand...

You need to use a VBA macro.

To enter the macro, <alt-F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, select the cell(s) you which to sort within the cell. Then
<alt-F8> opens the macro dialog box. Select the srt macro and Run.

=================================
Option Explicit
Sub srt()
Dim c As Range
For Each c In Selection
If Len(c.Text) > 0 Then c.Value = bSort(c.Text)
Next c
End Sub
Function bSort(str As String)
Dim temp As Variant
Dim tempArray() As Variant
Dim i As Integer
Dim NoExchanges As Integer

ReDim tempArray(0 To Len(str) - 1)
For i = 1 To Len(str)
tempArray(i - 1) = Mid(str, i, 1)
Next i

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 0 To UBound(tempArray) - 1

' If the element is greater than the element
' following it, exchange the two elements.
If tempArray(i) > tempArray(i + 1) Then
NoExchanges = False
temp = tempArray(i)
tempArray(i) = tempArray(i + 1)
tempArray(i + 1) = temp
End If
Next i
Loop While Not (NoExchanges)
bSort = Join(tempArray, "")
End Function
========================================
--ron
 
Back
Top