MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### how to sort a string?

• Follow

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

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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

```
 0

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

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

```
 0

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

```
 0

```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
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("IV1:IV" & LastRow)
.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
```
 0

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

```
 0

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

```
 0

```Hi All,

Thanks for your suggestions. I will

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
>

```
 0

7 Replies
165 Views

Similiar Articles:

7/17/2012 6:13:54 PM