try this udf,i came across it when looking for something to sort ip addresses ,insert in a new module then use as =NormDigits(a1) drag down then sort by that column
1.3.2.1
1.2.3
1.2.3.1
5.1.3
5.5.19.3.4.3.2.1.1.1
5.3.1
5.1
1.2.1
1.1.2
5.5.18.3.4.3.2.1.1.2
sorts as
1.1.2
1.2.1
1.2.3
1.2.3.1
1.3.2.1
5.1
5.1.3
5.3.1
5.5.18.3.4.3.2.1.1.2
5.5.19.3.4.3.2.1.1.1
code from here http://dmcritchie.mvps.org/excel/sorttcp.htm#chaptsort
Function NormDigits(cell As String, Optional p As Long) As String
'dmcritchie, worksheet.functions, 2004-01-21, _
' #[email protected]
'dmcritchie, newuser, 2004-07-01
Dim i As Long, n As String, s As String
Dim newstr As String
s = UCase(Trim(cell))
If p = 0 Then p = 3
newstr = ""
n = ""
reloop:
For i = 1 To Len(s)
If Mid(s, i, 1) Like "[0-9]" Then
n = n & Mid(s, i, 1)
ElseIf n = "" Then
newstr = newstr & Mid(s, i, 1)
Else
newstr = newstr & Format(n, Left("0000000", p))
n = ""
newstr = newstr & Mid(s, i, 1)
End If
Next i
If n <> "" Then newstr = newstr & Format(n, Left("0000000", p))
NormDigits = newstr
End Function
Bookmarks