Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAN YOU ALPHA SORT IN A SINGLE CELL?
CAN YOU ALPHA SORT IN A SINGLE CELL?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sort ascending or descending in multiple entries within one single cell? | Excel Discussion (Misc queries) | |||
Alpha Numeric Sort | Excel Discussion (Misc queries) | |||
Can I change the sort order to Alpha before Numeric? | Excel Discussion (Misc queries) | |||
Alpha sort excel mail list | Excel Discussion (Misc queries) | |||
I want to sort worksheets in a workbook into Alpha order? | Excel Worksheet Functions |