# Sort array or sort string other way

#### Temporary-Failure

##### Board Regular
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.

### 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
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
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)

#### Temporary-Failure

##### Board Regular
Absolutely super! Thanks. I'll send you a postcard.

#### mikerickson

##### MrExcel MVP
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:

#### Fluff

##### MrExcel MVP, Moderator
Absolutely super! Thanks. I'll send you a postcard.

You're welcome & thanks for the feedback

Replies
1
Views
24
Replies
10
Views
159
Replies
5
Views
39
Replies
7
Views
108
Replies
4
Views
40