Sorting comma strings

  • Thread starter Thread starter Ken
  • Start date Start date
K

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
 
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.
 
Back
Top