Sorting long numbers
Author Message
Sorting long numbers

Hello -- I am having problems with Word's built-in sort function.  I
have a table that has a column of numbers like:
3.2.8
3.2.5
3.2.10

When I use the built-in sort, it gets rearranged like:

3.2.10
3.2.5
3.2.8

Is there a better way to sort these values that will make the 3.2.10
come after the 3.2.9?

Here is the snippet of code that I am using:

With ReqTable
.Select
SortFieldType:=wdSortFieldAlphanumeric, _
SortOrder:=wdSortOrderAscending
End With

Thanks so much!

Tue, 12 Oct 2004 20:24:38 GMT
Sorting long numbers
Hi Jennifer,

I think you're going to have a difficult time with it. That is, 3.2.10 is
always smaller than 3.2.2-3.2.9 (two-tenths through nine-tenths is larger
than one-tenth)  Here's something that I came across on the MS KB site:
-------------------------
Word sorts numbers in the same way that it sorts alphabetic entries, that
is, one character at a time. For example, Word places all numbers that begin
with 1 (such as 12, 156, or 1147) before all numbers that begin with 2 (such
as 22, 256, or 2276). If two or more items begin with the same character,
Word evaluates subsequent characters in each item to determine which item
should come first. For example, Word places 124 before 19, based on the
second digit (2 is smaller than 9, so 124 comes before 19).
-------------------------
I'd suggest a workaround on your part (if you can). Instead of
3.2.8
3.2.5
3.2.10
Can you use the following:
3.2.08
3.2.05
3.2.10

HTH

Quote:
> Hello -- I am having problems with Word's built-in sort function.  I
> have a table that has a column of numbers like:
> 3.2.8
> 3.2.5
> 3.2.10

> When I use the built-in sort, it gets rearranged like:

> 3.2.10
> 3.2.5
> 3.2.8

> Is there a better way to sort these values that will make the 3.2.10
> come after the 3.2.9?

> Here is the snippet of code that I am using:

>   With ReqTable
>       .Select
>       .Sort ExcludeHeader:=True, FieldNumber:="Column 1", _
>          SortFieldType:=wdSortFieldAlphanumeric, _
>          SortOrder:=wdSortOrderAscending
>    End With

> Thanks so much!

Tue, 12 Oct 2004 21:00:41 GMT
Sorting long numbers
I used also, for a similar problem, an hidden text near the text I wanted to
sort:
{aa}3.2.8
{ab}3.2.5
{ac}3.2.10

or you could create a temporary column with the aa, ab, ac and after sorting
delete it again.
regards, Ali

Quote:
> Hi Jennifer,

> I think you're going to have a difficult time with it. That is, 3.2.10 is
> always smaller than 3.2.2-3.2.9 (two-tenths through nine-tenths is larger
> than one-tenth)  Here's something that I came across on the MS KB site:
> -------------------------
> Word sorts numbers in the same way that it sorts alphabetic entries, that
> is, one character at a time. For example, Word places all numbers that
begin
> with 1 (such as 12, 156, or 1147) before all numbers that begin with 2
(such
> as 22, 256, or 2276). If two or more items begin with the same character,
> Word evaluates subsequent characters in each item to determine which item
> should come first. For example, Word places 124 before 19, based on the
> second digit (2 is smaller than 9, so 124 comes before 19).
> -------------------------
> I'd suggest a workaround on your part (if you can). Instead of
> 3.2.8
> 3.2.5
> 3.2.10
> Can you use the following:
> 3.2.08
> 3.2.05
> 3.2.10

> HTH

> > Hello -- I am having problems with Word's built-in sort function.  I
> > have a table that has a column of numbers like:
> > 3.2.8
> > 3.2.5
> > 3.2.10

> > When I use the built-in sort, it gets rearranged like:

> > 3.2.10
> > 3.2.5
> > 3.2.8

> > Is there a better way to sort these values that will make the 3.2.10
> > come after the 3.2.9?

> > Here is the snippet of code that I am using:

> >   With ReqTable
> >       .Select
> >       .Sort ExcludeHeader:=True, FieldNumber:="Column 1", _
> >          SortFieldType:=wdSortFieldAlphanumeric, _
> >          SortOrder:=wdSortOrderAscending
> >    End With

> > Thanks so much!

Wed, 13 Oct 2004 00:30:15 GMT
Sorting long numbers
Another work-around would be to temporarily replace the periods with
some other character:
3/2/8
3/2/5
3/2/10

Now you can define this character ("/") as the field separator, and
sort on the three fields:

Selection.Sort _
FieldNumber:=1, SortFieldType:=wdSortFieldNumeric, _
SortOrder:=wdSortOrderAscending, _
FieldNumber2:=2, SortFieldType2:=wdSortFieldNumeric, _
SortOrder2:=wdSortOrderAscending, _
FieldNumber3:=3, SortFieldType3:=wdSortFieldNumeric, _
SortOrder3:=wdSortOrderAscending, _
Separator:="/", _
SortColumn:=False

Regards, Klaus

Wed, 13 Oct 2004 06:22:09 GMT
Sorting long numbers
Thanks so much for the insight and help.  Changing the actual number
format isn't really an option, so I am writing 2 functions -- one that
adds in the leading zeros before the sort and one that strips them
back out after the sort.  I found that this method works best for the
complex numbers that I am sorting (up to 10 levels deep.)

Fri, 15 Oct 2004 20:25:05 GMT
Sorting long numbers
Here is the code that I used to insert and delete leading zeroes.  For
completeness, I used this code to sort heading numbers of the format:
REQ. - 3.2.10.3.1.1.1
This gets around the issue of having 3.2.10.... come before 3.2.2....

Apologies for perhaps not being the most elegant code.  But it works!

Function InsertZero(reqnum As String) As String
Dim temp, finished  As String

sectionNumbers = Split(reqnum, ".")

For Each temp In sectionNumbers
If Not IsNumeric(temp) Then
finished = temp
Else
Do While Len(temp) < 3
temp = 0 & temp
Loop
'stick all of the temps together into return value
finished = finished & "." & temp
End If

Next

InsertZero = finished

End Function

Function DeleteZero(reqnumtemp As String)
Dim temp, finished  As String

sectionNumbers = Split(reqnumtemp, ".")

For Each temp In sectionNumbers
temp = Replace(temp, vbCr, "")
Count = 2
Do While Left(temp, 1) = "0"
temp = Right(temp, Count)
Count = Count - 1
Loop
'stick all of the temps together into return value
finished = finished & "." & temp
Next

DeleteZero = finished

End Function

Fri, 15 Oct 2004 23:50:19 GMT

 Page 1 of 1 [ 6 post ]

Relevant Pages