Sorting comma strings


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
 
Ad

Advertisements

D

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

Advertisements

K

Ken

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top