Sorting Problem

[Iain McCormick]

I have produced a document that I need to be sorted into
numerical order. Column A is the number of the entry and
is set out like 1.1, 1.2.1, 1.10.1.2.3 as so on.

When I attempt to sort these into numerical order it comes
out like:

1.1
1.10
1.11
1.12
1.2
1.22
1.3
ETC. instead of

1.1
1.2
1.3
1.10
1.11
1.12
1.22

The cells are currently formatted as text so I can have
the multiple .'s in there. So 2 questions please

1. Is there a way of formatting the cells to have them as
a number, and keep the multiple .'s in

2. Is there a way of formatting so 1.10 comes after 1.2
etc.

Thank you for your help and time

 

[Mike]

There are a few options, but because you have a sample
with 4 decimal places (1.10.1.2.3), it will be very
tricky. One option is to define a Custom List in
Tools/Options but if you have a thousand document numbers,
then this gets too complex. You might want to add leading
zeros so instead of 1.1 and 1.10, you could use 1.01 and
1.10. You can achieve this with a series of string
operators in a spare column (FIND(), MID() and RIGHT()
functions) but because you have 4 decimals, that becomes
farily involved and unfortunatly I just ran out of time,
and have to get back to work.

 

[Paul Vovk]

Try this (and create an xtra column in your spreadsheet
that uses this function and takes the 1.1.2.3 cell as the
argument, then, sort on this dummy xtra column using
Excel):

Function makeSortNum(textNum As String) As Long
Dim i As Integer
Dim subSort As String
Dim char As String
Dim iSortNum As Long

i = 0
For i = 1 To Len(textNum)
subSort = ""
While (Mid(textNum, i, 1) <> ".") And i <= Len
(textNum)
subSort = subSort & Mid(textNum, i, 1)
i = i + 1
Wend
iSortNum = iSortNum + subSort
Next i
makeSortNum = iSortNum
End Function

 

[Myrna Larson]

I think there's a problem here. You just sum the "pieces" of the number without taking into
account their position. I just tried your function with the inputs 1.2.3 and 3.2.1 It returns 6
for both. These obviously won't sort correctly <g>.

If you want to allow for 2-digit numbers in each section, you can modify this line

iSortNum = iSortNum + subSort

to

iSortNum = iSortNum * 100 + subSort

With that change, you will get 10203 and 30201, which will sort correctly.

Since you change the value of i inside the For/Next loop, it's better to change your outer loop
from For/Next to Do/Loop or While/Wend.

BUT... if you have Excel 2000, the Split function will work much more quickly to separate the
original "number" at the decimal points than will scanning character by character with two calls
to Mid() (which returns a variant -- should use Mid$(), which returns a string, here)

You could also convert the text to a byte array and use numeric functions to "parse" it, i.e.

Function MakeSortNum2(TextNum As String) As Double
Dim B() As Byte
Dim i As Byte
Dim N As Long
Dim NN As Double

Const Multiplier As Long = 100

B() = TextNum

NN = 0
N = 0
For i = 0 To UBound(B()) Step 2
If B(i) >= 48 And B(i) <= 57 Then '0-9
N = N * 10 + B(i) - 48
ElseIf B(i) = 46 Then 'decimal point
NN = NN * Multiplier + N
N = 0
End If
Next i
NN = NN * Multiplier + N
MakeSortNum2 = NN
End Function

Note that I made this function a Double instead of a Long, to increase the range (the OP showed
5 segments with 1 or 2 digits). It will accommodate 99.99.99.99.99 -> 99999999, but a Long will
not.

Try this (and create an xtra column in your spreadsheet
that uses this function and takes the 1.1.2.3 cell as the
argument, then, sort on this dummy xtra column using
Excel):

BUT... if you have Excel 2000, the Split function will work much more quickly to separate the
original "number" at the decimal points than will scanning character by character with two calls
to Mid() (which returns a variant -- should use Mid$(), which returns a string, here)