Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort a string?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort a string?
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/sh...d.php?t=166528 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort a string?
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort a string?
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort a string?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort a string?
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort a string?
"CG Rosen" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort a string?
Hi All,
Thanks for your suggestions. I will test all of them and learn more. Brgds CG Rosen "CG Rosen" skrev i meddelandet ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you sort one cell or a string in vba? | Excel Programming | |||
Sort does not preserve string of additions | Excel Worksheet Functions | |||
Convert a string value to numeric and sort | Excel Programming | |||
Sort collection string keys | Excel Programming | |||
sort (on part of) string - originally posted under Tricky Sort | Excel Programming |