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
      .Sort ExcludeHeader:=True, FieldNumber:="Column 1", _
         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  
 
 [ 6 post ] 

 Relevant Pages 

1. combine 2 long number into a long number

2. Alpha+Numeric sorting (long listing)

3. ORA-01467: sort key too long

4. Wildcards finding long numbers within text .

5. date shown as long number problem

6. converting a string to a long (string contains a Hex number)

7. Function to convert number to long english word

8. Q: INT - LONG NUMBER - ARRAY

9. How to cut a long number short?

10. long numbers

11. Converting string to a number (long)

12. IS there any way for handling number bigger then Long value in VB

 

 
Powered by phpBB® Forum Software