Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Sorting comma strings
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 -- Ken Elder University of Oklahoma |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Sorting comma strings
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 ' --------------------------------------------------- "Ken" wrote in message ... 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 -- Ken Elder University of Oklahoma |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Sorting comma strings
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" wrote in message ... 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 -- Ken Elder University of Oklahoma |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Sorting comma strings
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" wrote in message ... 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 -- Ken Elder University of Oklahoma |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Sorting comma strings
Thanks to both Steve Yandl and Doug Robbins! Both of theirmacros do exactly
what we need. -- Ken Elder University of Oklahoma "Ken" wrote: 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 -- Ken Elder University of Oklahoma |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Sorting comma strings
Thanks to both Steve Yandl and Doug Robbins! Both of theirmacros do exactly what we need. -- Ken Elder University of Oklahoma "Ken" wrote: 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 -- Ken Elder University of Oklahoma |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Strings? | Microsoft Word Help | |||
Mergefield with comma, suppress comma if field blank | Mailmerge | |||
Manipulate Strings in Word | Microsoft Word Help | |||
Finding strings of the chacter ^ | New Users | |||
Make Word displays strings of text, not strings of code | Microsoft Word Help |