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


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=166528

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

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, ",")



"CG Rosen" <carlgran.rosen@telia.com> wrote in message 
news:23056155-A249-4AAC-8A78-620753C038C6@microsoft.com...
> 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 

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

"CG Rosen" <carlgran.rosen@telia.com> wrote in message 
news:23056155-A249-4AAC-8A78-620753C038C6@microsoft.com...
> 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 

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
"CG Rosen" wrote:

> 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

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


"CG Rosen" <carlgran.rosen@telia.com> wrote in message 
news:23056155-A249-4AAC-8A78-620753C038C6@microsoft.com...
> 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 

"CG Rosen" <carlgran.rosen@telia.com> wrote in message 
news:23056155-A249-4AAC-8A78-620753C038C6@microsoft.com...
> 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

Hi All,

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

Brgds

CG Rosen

"CG Rosen" <carlgran.rosen@telia.com> skrev i meddelandet 
news:23056155-A249-4AAC-8A78-620753C038C6@microsoft.com...
> 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
>