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.

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)))


"Ladymuck" wrote:

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.

This is not easy!
There is a user defined function (a macro) he'Sorting TCP/IP
Addresses and the like'
(http://www.mvps.org/dmcritchie/excel....htm#chaptsort) which has
this function in:Function ChaptSort(cell As String) As
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 number
(actually text).
Add the macro (there's a link to *Getting Started with Macros and User
Defined Functions* at the top of the website linked above) then in the
worksheet add the formulae in a temporarily added column and sort on
that column, then delete that column. That's it!
If there are more than 4 numbers between full stops in your legal
numbering (some call it Outline numbering) it'll fail, but can be
amended if this is the case.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126308

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!