how to sort a string?

[CG Rosen]

Hi Group

Trying to sort a string looking like:

bv-8,ok-3,bv-5,sk-1,bh-2,ok-9 etc

into:

bh-2,bv-5,bv-8,ok-3,ok-9,sk-1 etc

Grateful for some help.

Brgds

CG Rosen

 

[joel]

Try This code.

Sub SortData()

InputStr = "bv-8,ok-3,bv-5,sk-1,bh-2,ok-9"
'put data into an array
SortArray = Split(InputStr, ",")

For I = LBound(SortArray) To (UBound(SortArray) - 1)
For J = (I + 1) To UBound(SortArray)
If SortArray(I) > SortArray(J) Then
temp = SortArray(I)
SortArray(I) = SortArray(J)
SortArray(J) = temp
End If
Next J
Next I

OutputStr = Join(SortArray, ",")
End Sub

 

[Bob Phillips]

Split the string into an array

ary = Split(sData, ",")

Drop that array onto a worksheet

Set rng = Range("A1").Resize(UBound(ary) - LBound(ary) + 1)
rng = Application.Transpose(ary)

Sort the range

rng.Sort key1:=Range("A1"), order1:=xlAscending, header:=xlNo

Pull that range back into the array

ary = Application.Transpose(rng)

Then join the array back into the string

sData = Join(ary, ",")

 

[Harald Staff]

Hi CG

Sub test()
MsgBox SortString("bv-8,ok-3,bv-5,sk-1,bh-2,ok-9", ",")
End Sub

Function SortString(S As String, Separator As String) As String
Dim X() As String
Dim tmp As String
Dim i As Long, j As Long
'split:
X = Split(S, ",")
'trim:
For i = LBound(X) To UBound(X)
X(i) = Trim$(X(i))
Next
'sort:
For i = LBound(X) To UBound(X) - 1
For j = LBound(X) To UBound(X) - 1
If X(j) > X(j + 1) Then
tmp = X(j + 1)
X(j + 1) = X(j)
X(j) = tmp
End If
Next
Next
'rebuild:
tmp = ""
For i = LBound(X) To UBound(X)
tmp = tmp & X(i) & ","
Next
tmp = Left(tmp, Len(tmp) - 1)
SortString = tmp
End Function

HTH. Best wishes Harald

 

[Mike H]

Hi,

This works on A1 in the activesheet. It cheats by copying the string to a
new range, sorting it then putting it back. It uses column IV as the
temporary sort range so any data in that column will be lost.


Sub Sonic()
Dim x As Long, LastRow As Long
Dim V As Variant
Dim S As String, newstring As String
Columns(256).ClearContents
S = Range("A1").Value
V = Split(S, ",")
Application.ScreenUpdating = False
For x = 0 To UBound(V)
Cells(x + 1, 256).Value = V(x)
Next
LastRow = Cells(Cells.Rows.Count, "IV").End(xlUp).Row
Worksheets("Sheet1").Sort.SortFields.Clear
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("IV1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("IV1:IV" & LastRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set MyRange = Range("IV1:IV" & LastRow)
For Each c In MyRange
newstring = newstring & c.Value & ","
Next
Range("a1").Value = Left(newstring, Len(newstring) - 1)
Columns(256).ClearContents
Application.ScreenUpdating = True
End Sub



Mike

 

[Peter T]

And here's another one, spoilt for choice !

Sub test()
Dim S As String, sorted As String
S = "bv-8,ok-3,bv-5,sk-1,bh-2,ok-9"
sorted = SortCommaSepString(S)

Debug.Print sorted ' bh-2,bv-5,bv-8,ok-3,ok-9,sk-1
End Sub

Function SortCommaSepString(ByVal strIn) As String
Dim i As Long, j As Long
Dim s1 As String, s2 As String
Dim arr() As String

arr = Split(strIn, ",")

For i = 0 To UBound(arr) - 1
For j = (i + 1) To UBound(arr)
s1 = arr(i)
s2 = arr(j)
If StrComp(s1, s2, vbTextCompare) = 1 Then
arr(i) = s2
arr(j) = s1
End If
Next j
Next i

SortCommaSepString = Join(arr, ",")

End Function


All posted examples will give similar results with the test sample. However
with different strings the results might be very different, as three
different comparison methods are used. Bob's will sort the way Excel does
though it means using cells. Otherwise for most string comparisons it's
better (I think) to use the StrComp function rather than simple greater/less
comparisons.

Regards,
Peter T

 

[Lars-Åke Aspelin]

Hi Group

Trying to sort a string looking like:

bv-8,ok-3,bv-5,sk-1,bh-2,ok-9 etc

into:

bh-2,bv-5,bv-8,ok-3,ok-9,sk-1 etc

Grateful for some help.

Brgds

CG Rosen

Try this macro:

Sub sort_string(source As Range, destination As Range)
Dim parts() As String
Dim sorted As String
parts = Split(source.Value, ",")
imax = UBound(parts)
For i = 0 To imax - 1
For j = i + 1 To imax
If parts(j) < parts(i) Then
tmp = parts(i)
parts(i) = parts(j)
parts(j) = tmp
End If
Next j
Next i
sorted = ""
For i = 0 To imax - 1
sorted = sorted & parts(i) & ","
Next i
sorted = sorted & parts(imax)
destination.Value = sorted
End Sub

Example:

sort_string Range("A1"), Range("B4")

will sort the string in cell A1 and put the result in cell B4

Hope this helps / Lars-Åke

 

[CG Rosen]

Hi All,

Thanks for your suggestions. I will
test all of them and learn more.

Brgds

CG Rosen