Is there a way to sort numbers in one cell from smallest to largest?

I have a range of cells that have entries with numbers separated by spaces. Like this "'11 12 1 2 3 4 5 6 7 8".  Is there a macro that will sort them smallest to largest: "'1 2 3 4 5 6 7 8 11 12". If it makes it easier I could replace the spaces with commas or something. .

I know I can do this with text to columns, sort the resulting range and then overwrite the cell with a concatenation. In this working code I'm doing that with cell F735
Dim cel As Range, lenStr As Long, xNum As Long, sumAll As String
lenStr = Len([F735])
xNum = lenStr - Len(Replace([F735], " ", "")) + 1

[F735].TextToColumns Destination:=[AL735], DataType:=xlDelimited _
    , TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
Range("AL735:AU735").Sort key1:=[AL735], order1:=xlAscending
sumAll = "=AL735&"" ""&AM735&"" ""&AN735&"" ""&AO735&"" ""&AP735&"" ""&AQ735&"" ""&AR735&"" ""&AS735&"" ""&AT735&"" ""&AU735"
[F735] = Evaluate(sumAll)

Open in new window

But even with this, I would need a way to determine how many items should be in the array to run the text to columns. The first 3 lines are my attempt do that but I'm not sure how to carry that over into the array code.

The ideal solution would be to do it all in code, but the text to columns workaround would be fine too.

Any thoughts?

Thanks,
john
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
John,

Can you post your sample file as it will become easy to write a macro for you..

Saurabh...
0
byundtMechanical EngineerCommented:
John,
Here is a macro that will sort the values in the active cell into ascending numeric order:
Sub SortMe()
'Sorts the active cell in ascending order
Dim temp As Variant, v As Variant
Dim i As Long, j As Long, n As Long
Dim b As Boolean
Const delimiter As String = " "
v = Split(ActiveCell.Value, delimiter)
n = UBound(v)
If n > 0 Then
    For i = 0 To n
        b = False
        For j = 1 To n
            If CDbl(v(j)) < CDbl(v(j - 1)) Then
                b = True
                temp = v(j)
                v(j) = v(j - 1)
                v(j - 1) = temp
            End If
        Next
        If b = False Then Exit For
    Next
    ActiveCell.Value = Join(v, delimiter)
End If
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
It won't make a lot of difference at best but this is probably faster. It assumes the data in in column A. If it's not in "A" then change lines 7, 9 and 11.

Sub SortCell()
Dim strParts() As String
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngNdx As Long

lngLastRow = Range("A1048576").End(xlUp).Row
For lngRow = 1 To lngLastRow
    strParts = Split(Cells(lngRow, "A"))
    QuickSort strParts, LBound(strParts), UBound(strParts)
    Cells(lngRow, "A") = Join(strParts, " ")
Next
End Sub
Private Sub QuickSort(C() As String, ByVal First As Long, ByVal Last As Long)

Dim Low As Long, High As Long
Dim MidValue As String

Low = First
High = Last
MidValue = C((First + Last) \ 2)

Do
While C(Low) < MidValue
Low = Low + 1
Wend

While C(High) > MidValue
High = High - 1
Wend

If Low <= High Then
Swap C(Low), C(High)
Low = Low + 1
High = High - 1
End If
Loop While Low <= High

If First < High Then QuickSort C, First, High
If Low < Last Then QuickSort C, Low, Last
End Sub
Private Sub Swap(ByRef A As String, ByRef B As String)
Dim T As String

T = A
A = B
B = T
End Sub

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

byundtMechanical EngineerCommented:
I modified my sub so prior to the sort, it would remove duplicated delimiters, leading delimiters and trailing delimiters. I also changed the sub to work on a preselected range of cells, even including an entire column. If you want to determine the range to be sorted programmatically, Martin Liss' code does that nicely.

It is worth noting that my suggested code will sort 11 after 2 (as you requested). Martin Liss' code puts the 11 before the 2.
Sub SortMe()
'Sorts the contents of preselected cells in ascending order
Dim temp As Variant, v As Variant
Dim i As Long, j As Long, n As Long
Dim B As Boolean
Dim cel As Range, rg As Range
Const delimiter As String = " "         'Change to any desired string used as a delimiter
Application.ScreenUpdating = False
Set rg = Selection          'Or set rg to a predefined range
Set rg = Intersect(rg, rg.Worksheet.UsedRange)
For Each cel In rg.Cells
    Set v = Nothing
    v = cel.Value
    For i = 1 To 4          'Remove up to four successive delimiters
        v = Replace(v, delimiter & delimiter, delimiter)
    Next
    If Left(v, Len(delimiter)) = delimiter Then v = Mid(v, Len(delimiter) + 1)          'Remove leading delimiter
    If Right(v, Len(delimiter)) = delimiter Then v = Left(v, Len(v) - Len(delimiter))   'Remove trailing delimiter
    v = Split(v, delimiter)
    n = UBound(v)
    If n > 0 Then
        For i = 0 To n
            B = False
            For j = 1 To n
                If Val(v(j)) < Val(v(j - 1)) Then
                    B = True
                    temp = v(j)
                    v(j) = v(j - 1)
                    v(j - 1) = temp
                End If
            Next
            If B = False Then Exit For
        Next
        cel.Value = Join(v, delimiter)
    End If
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Martin Liss' code puts the 11 before the 2.
Good catch.

I changed QuickSort to sort numerically rather than by string value.

Private Sub QuickSort(C() As String, ByVal First As Long, ByVal Last As Long)

Dim Low As Long, High As Long
Dim MidValue As String

Low = First
High = Last
MidValue = C((First + Last) \ 2)
Debug.Print MidValue
Do
While Val(C(Low)) < Val(MidValue)
Low = Low + 1
Wend

While Val(C(High)) > Val(MidValue)
High = High - 1
Wend

If Low <= High Then
Swap C(Low), C(High)
Low = Low + 1
High = High - 1
End If
Loop While Low <= High

If First < High Then QuickSort C, First, High
If Low < Last Then QuickSort C, Low, Last
End Sub

Open in new window

0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
byundt, it's been so long I've forgotten your fist name, but the code works perfectly, thanks! However there are several lines I don't understand and if you don't mind when I have time to form intelligent questions I want to find out.

Martin, I couldn't test yours (and this is where I show how pathetic my understanding of certain basic things is after all these years), but I can't figure out how to debug or even run your Private Sub. I can't assign it to a button, and i can't F8 my way through it or run it from within VBE.

~ John
0
Martin LissOlder than dirtCommented:
Here's a workbook that uses my code. To use it just run SortCell or assign it to a button. QuickSort and Swap are just two routines that are called by SortCell. You don't need to do anything with them - they just need to be present.
28714100.xlsm
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Martin, I got it to work. I want to award the majority of the points to Brad since his code worked right out of the box for me and it is faster and more compact, but I really appreciate your sticking with it and posting the workbook. I hope the 100 points is okay.

~ John
0
Martin LissOlder than dirtCommented:
I have no problem with what you decided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.