Sort legal numbering

L

Ladymuck

I am using Excel 2003.

I have a spreadsheet where the data is organised with legal numbering and
then some text. The problem is that the order when using the standard sort is
(for example) 7.1, 7.10, 7.11, 7.12, 7.2, 7.3, 7.31, 7.32, 7.4, instead of
7.1, 7.2, 7.3, 7.4, 7.10, 7.11, 7.12, etc

How do I get excel to recognise that 7.1 should be followed by 7.2 and not
7.10? The options button on the sort dialog box only offers Normal or months
of the year.

Thanks for any suggestions.
 
J

Joel

I usually have to use auxilarary columns which will contain 7 in one column
and 1 in next column

Use these formula to get the integer and fractional part of the number.
then copy the formula down the new columns. then sort on new columns

=VALUE(LEFT(A1,FIND(".",A1)-1))
=VALUE(MID(A1,FIND(".",A1)+1,LEN(A1)))
 
P

p45cal

This is not easy!
There is a user defined function (a macro) here:'Sorting TCP/I
Addresses and the like
(http://www.mvps.org/dmcritchie/excel/sorttcp.htm#chaptsort) which ha
this function in:Function ChaptSort(cell As String) A
String
'dmcritchie
Dim i As Long, j As Long, n As Long
Dim oldstr As String, newstr As String
oldstr = cell
i = 1
newstr = ""
reloop:
j = InStr(Mid(oldstr, i), ".")
If j > 5 Then
ChaptSort = "#segment"
Exit Function
ElseIf j <> 0 Then
l = j - l
newstr = newstr & "." & Left("0000", 5 - j) & Mid(oldstr, i, j - 1)
i = i + j
GoTo reloop
Else
If Len(oldstr) - i >= 4 Then
ChaptSort = "#length"
Exit Function
Else
newstr = newstr & "." & Left("0000", 3 - (Len(oldstr) - i))
Mid(oldstr, i)
End If
End If
ChaptSort = "*" & Mid(newstr, 2)
End FunctionThe formula in a worksheet would look like:
=ChaptSort(B27)
where B27 contains the legal numbering. It makes a sortable numbe
(actually text).
Add the macro (there's a link to *Getting Started with Macros and Use
Defined Functions* at the top of the website linked above) then in th
worksheet add the formulae in a temporarily added column and sort o
that column, then delete that column. That's it!
If there are more than 4 numbers between full stops in your lega
numbering (some call it Outline numbering) it'll fail, but can b
amended if this is the case
 
L

Ladymuck

Thanks to you both for your suggestions, it's a shame there isn't an easier
way but at least I know my sanity is (roughly) intact!
 
Top