CAN YOU ALPHA SORT IN A SINGLE CELL?
 

CAN YOU ALPHA SORT IN A SINGLE CELL?

CAN YOU ALPHA SORT IN A SINGLE CELL?
 

Yes. But it requires a VBA macro
--
Gary''s Student - gsnu200755


"Precious Pearl" wrote:

CAN YOU ALPHA SORT IN A SINGLE CELL?

CAN YOU ALPHA SORT IN A SINGLE CELL?
 

No, sort works on a Row basis. You need more than one Row of data.

Precious Pearl wrote:

CAN YOU ALPHA SORT IN A SINGLE CELL?

CAN YOU ALPHA SORT IN A SINGLE CELL?
 

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.


On Nov 8, 3:01 pm, Gary''s Student
wrote:
Yes. But it requires a VBA macro
--
Gary''s Student - gsnu200755



"Precious Pearl" wrote:
CAN YOU ALPHA SORT IN A SINGLE CELL?- Hide quoted text -

- Show quoted text -

CAN YOU ALPHA SORT IN A SINGLE CELL?
 

On Thu, 8 Nov 2007 11:31:01 -0800, Precious Pearl
wrote:

CAN YOU ALPHA SORT IN A SINGLE CELL?

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