Sorting comma strings

[Ken]

We frequently work with strings of numbers separated by commas (e.g., 6, 55,
8, 341, 21, 4). We sort those numbers by selecting them, replacing commas
with paragraph marks, sorting, then replacing paragraph marks with commas.

When I record a macro to do that after the numbers are selected, it does the
replace/sort/replace on all number strings in the whole document rather than
on only the selected numbers.

Is there something I can do either while recording or by ediging the macro
so that it will sort only the selected numbers?

We use Word 2003 on Windows XP.

I'll paste the macro below:

Sub commaSort()
'
' commaSort Macro
' Macro recorded 2/18/2010 by Ken Elder
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ","
.Replacement.Text = "^p"
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Sort ExcludeHeader:=False, FieldNumber:="Paragraphs", _
SortFieldType:=wdSortFieldNumeric, SortOrder:=wdSortOrderAscending, _
FieldNumber2:="", SortFieldType2:=wdSortFieldAlphanumeric,
SortOrder2:= _
wdSortOrderAscending, FieldNumber3:="", SortFieldType3:= _
wdSortFieldAlphanumeric, SortOrder3:=wdSortOrderAscending,
Separator:= _
wdSortSeparateByTabs, SortColumn:=False, CaseSensitive:=False,
LanguageID _
:=wdEnglishUS, SubFieldNumber:="Paragraphs", SubFieldNumber2:= _
"Paragraphs", SubFieldNumber3:="Paragraphs"
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p"
.Replacement.Text = ","
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

 

[Steve Yandl]

Ken,

The approach below is slightly different but I think it will do what you
want. It takes the selection, breaks apart by commas, sorts the recordset
and then recreates the list of numbers and commas as a new string that gets
typed as a replacement for the original set of numbers. I've gone with the
assumption that your numbers are all integers.

Steve Yandl

' ---------------------------------------------------
Sub SortSelectionCSVs()

Const adInteger = 3

Dim arrVals
Dim n As Integer
Dim strSorted As String

arrVals = Split(Selection.Text, ",")

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adInteger
DataList.Open

For n = 0 To UBound(arrVals)
DataList.AddNew
DataList("MyList") = CInt(arrVals(n))
DataList.Update
Next n

DataList.Sort = "MyList"

strSorted = ""

DataList.MoveFirst
Do Until DataList.EOF
strSorted = strSorted & DataList.Fields.Item("MyList") _
& ", "
DataList.MoveNext
Loop

strSorted = Left(strSorted, Len(strSorted) - 2)

Selection.TypeText Text:=strSorted

Set DataList = Nothing

End Sub



' ---------------------------------------------------

 

[Doug Robbins - Word MVP]

Use:

Dim rngSort As Range
Dim rngArray As Variant
Dim i As Long, j As Long
Dim strSorted As String
Set rngSort = Selection.Range
rngArray = Split(rngSort, ", ")
For i = 0 To UBound(rngArray) - 1
For j = i + 1 To UBound(rngArray)
If Val(rngArray(i)) > Val(rngArray(j)) Then
temp = rngArray(j)
rngArray(j) = rngArray(i)
rngArray(i) = temp
End If
Next j
Next i
For i = 0 To UBound(rngArray)
strSorted = strSorted & Str(rngArray(i)) & ","
Next i
strSorted = Left(strSorted, Len(strSorted) - 1)
rngSort.text = strSorted


--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

 

[Ken]

Thanks to both Steve Yandl and Doug Robbins! Both of theirmacros do exactly
what we need.