PDA

View Full Version : Sorting inside single cell



Eugene
03-17-2007, 09:16 AM
I have up to 40(numbers) values in 1 cell separated by space ( exemple 1243 724 13 6405 523 and so on) and need to sort them in ascending order ( 13 523 724 1243 ) inside that cell. Is it possible? I have Exel 2002

Eugene
03-17-2007, 09:47 AM
I have up to 40(numbers) values in 1 cell separated by space ( exemple 1243 724 13 6405 523 and so on) and need to sort them in ascending order ( 13 523 724 1243 ) inside that cell. Is it possible? I have Exel 2002 Or, if I sort the numbers in separate cells in ascending order, how can I put them in 1 cell separated by space?

Shazam
03-17-2007, 10:13 AM
I have up to 40(numbers) values in 1 cell separated by space ( exemple 1243 724 13 6405 523 and so on) and need to sort them in ascending order ( 13 523 724 1243 ) inside that cell. Is it possible? I have Exel 2002

If you download and install the free Morefunc.xll (http://xcell05.free.fr/) add-in then you can use this formula below. Input formula in cell B1 and copied down. The formula is an-array need to hold down:

Ctrl,Shift,Enter

=TRIM(MCONCAT(VSORT(EVAL("{"&SUBSTITUTE(A1," ",";")&"}"),,1)
&" "))


Hope it helps!

zoom38
03-17-2007, 10:15 AM
Eugene are you looking for a VBA solution or worksheet solution? As for the worksheet solution if you have the values sorted in column A, put in the cell you want =A1 & " " & A2 & " " & A3 & " " & A4 and so on.

I personally don't know how to sort within a cell. Sorry.

Good Luck
Gary

mdmackillop
03-17-2007, 02:20 PM
Sub SortCell()
Dim MyList, m, str As String

MyList = Split(Trim(Range("A1")), " ")
'Sort the array
Quick_Sort MyList, 0, UBound(MyList)

For Each m In MyList
str = str & m & " "
Next

Range("A2") = str

End Sub


Private Sub Quick_Sort(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long)
Dim Low As Long, High As Long
Dim Temp As Variant, List_Separator As Long
Low = CLng(First)
High = CLng(Last)
List_Separator = SortArray((First + Last) / 2)
Do
Do While (CLng(SortArray(Low)) < List_Separator)
Low = Low + 1
Loop
Do While (CLng(SortArray(High)) > List_Separator)
High = High - 1
Loop
If (Low <= High) Then
Temp = SortArray(Low)
SortArray(Low) = SortArray(High)
SortArray(High) = Temp
Low = Low + 1
High = High - 1
End If
Loop While (Low <= High)
If (First < High) Then Quick_Sort SortArray, First, High
If (Low < Last) Then Quick_Sort SortArray, Low, Last
End Sub

Eugene
03-18-2007, 06:38 AM
Thanks everyone for help. I'll try