Sort array or sort string other way

Temporary-Failure

Board Regular
Joined
Jul 16, 2010
Messages
137
I have cell A1 which has string: -18 -11 -15 -8 -2 -5 -20 -14 -10 -7 0 -13 -19 -4 -1 -12 -6 -3 -9
I need to sort this string to be like: 0 -1 -2 -3 -4 -5 -6 -7 -8 -9 -10 -11 -12 -13 -14 -15 -16 -17 -18 -19 -20

Or string: 18 11 15 8 2 5 20 14 10 7 0 13 19 4 1 12 6 3 9
To be like 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

There's nice sorting way here: https://stackoverflow.com/questions/152319/vba-array-sort-function
It sorts ie. 22 29 21 25 23 27 24 26 20 28 30 nicely but it won't work in my cases.

I don't find solution. Could you help me? It doesn't have to be vba it can be also excel function if possible.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Function Sortstring(St As String, Ord As String) As String
   Dim Sp As Variant, tmp As Variant
   Dim i As Long, j As Long
   
   Sp = Split(St)
   For i = 0 To UBound(Sp)
      For j = i To UBound(Sp)
         If Ord = "a" Then
            If CLng(Sp(i)) > CLng(Sp(j)) Then
               tmp = Sp(i)
               Sp(i) = Sp(j)
               Sp(j) = tmp
            End If
         Else
            If CLng(Sp(i)) < CLng(Sp(j)) Then
               tmp = Sp(i)
               Sp(i) = Sp(j)
               Sp(j) = tmp
            End If
         End If
      Next j
   Next i
   Sortstring = join(Sp, " ")
End Function
Used like

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">-18 -11 -15 -8 -2 -5 -20 -14 -10 -7 0 -13 -19 -4 -1 -12 -6 -3 -9</td><td style=";">0 -1 -2 -3 -4 -5 -6 -7 -8 -9 -10 -11 -12 -13 -14 -15 -18 -19 -20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">18 11 15 8 2 5 20 14 10 7 0 13 19 4 1 12 6 3 9</td><td style=";">0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 18 19 20</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=Sortstring(<font color="Blue">A1,"d"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=Sortstring(<font color="Blue">A2,"a"</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
Another option depending on your data & required output
Code:
Function Sortstring(St As String) As String
   Dim Sp As Variant, tmp As Variant
   Dim i As Long, j As Long
   
   Sp = Split(St)
   For i = 0 To UBound(Sp)
      For j = i To UBound(Sp)
         If Abs(CLng(Sp(i))) > Abs(CLng(Sp(j))) Then
            tmp = Sp(i)
            Sp(i) = Sp(j)
            Sp(j) = tmp
         End If
      Next j
   Next i
   Sortstring = join(Sp, " ")
End Function
Used =Sortstring(A1)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
This is another sorting function. Note the SortAsNumbers argument can be set depending on whether you want "10" < "2" or 2 < 10
Code:
Function SortDelimitedString(ByVal aString As String, Optional SortAsNumbers As Boolean = False, _
                        Optional Delimiter As String = " ", Optional Descending As Boolean = False) As String
    Dim Words As Variant
    Dim strLeft As String, Pivot As String, strRight As String
    Dim i As Long, LT As Boolean

    Words = Split(aString, Delimiter)
    Pivot = Words(0)

    For i = 1 To UBound(Words)
        LT = Words(i) < Pivot
        If SortAsNumbers And IsNumeric(Pivot) And IsNumeric(Words(i)) Then
            LT = Val(Words(i)) < Val(Pivot)
        End If

        If LT Xor Descending Then
            strLeft = strLeft & Delimiter & Words(i)
        Else
            strRight = strRight & Delimiter & Words(i)
        End If
    Next i

    strLeft = Mid(strLeft, Len(Delimiter) + 1)
    strRight = Mid(strRight, Len(Delimiter) + 1)
    
    If 0 < Len(strLeft) Then
        Pivot = SortDelimitedString(strLeft, SortAsNumbers, Delimiter, Descending) & Delimiter & Pivot
    End If
    If 0 < Len(strRight) Then
        Pivot = Pivot & Delimiter & SortDelimitedString(strRight, SortAsNumbers, Delimiter, Descending)
    End If
    SortDelimitedString = Pivot
End Function
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,522
Messages
5,523,363
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top