Sorting by IP address??

[John]

How do I get my spreadsheet to sort by IP address?

That is x.x.x.2<->x.x.x.252

Right now numbers like x.x.x.110 are sorted before x.x.x.1

Thanks in advance,

John

[Ken Wright]

Knew this would come in handy. Following is my initial flawed attempt at answering the same
question previously, and then Harlan Grove's correction which should see you right. Use the
formula in a helper column to turn the addresses into values and then sort on that column.

"Harlan Grove" <hrl...@aol.com> wrote in message news:bJypa.1547$95....@www.newsranger.com...
"Ken Wright" wrote...
>Assuming your IP addresses start in A1 then put the following in B1 and
>copy down.
>
>=--SUBSTITUTE(A1,".","")
..

Unwise. IP addresses are 4 8-bit numbers (octets) concatenated with periods
between them. They don't always have leading zeros, meaning your approach would
only work if 1.1.1.1 always appeared as 001.001.001.001. If IP addresses could
appear without leading zeros, then you need to convert it into the 32-bit
unsigned integer that it actually represents. (Otherwise, 1.1.1.1 would appear
less than 0.0.1.100, which isn't the case.)

=INT(LEFT(A1,FIND(".",A1,FIND(".",A1)+1)-1))*2^24
+10*MOD(LEFT(A1,FIND(".",A1,FIND(".",A1)+1)-1),1)*2^16
+INT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,256))*2^8
+10*MOD(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,256),1)

--
Public Service Announcements:
1. Don't attach files to postings in this newsgroup.
2. Learn how to snip unnecessary text from quoted material. Indiscriminate
quoting wastes more bandwidth than file attachments.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

"John" <jdi...@hotmail.com> wrote in message news:06ca01c35a81$54d25f60$a301...@phx.gbl...

[John]

Ken,

Thanks for the reply. I'm a newbie to Excel, what do you
mean by a "helper column"?

John

>.
>

[Ken Wright]

A helper column is simply another column in the spreadsheet that you create based on the data in
your original column. This means you don't have to touch the data in your original column. It's
then simply referred to as a helper column, and once you have done what you needed with it, you
can then delete it if you want.

Assume your data is in Col A, starting in A2 (Header in A1), then make Col B a helper column, and
in B2 put the formula given and then copy down.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

<snip>

[David McRitchie]

Wasn't expecting the decimal point but it won't affect sorting.

1.1.22.33 16848387.3
1.1.22.34 16848387.4

You could run a macro and put everything into the same
format, leading zeros are just as valid for IP addresses.
http://www.mvps.org/dmcritchie/excel/sorttcp.htm

001.001.022.033
001.001.022.034
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote =..

[John]

David,

Thanks for the pointers! I'm trying to implement this on
my spreadsheet. How do you add a user defined function
like IPSort? Is this the way to do it or should I try a
macro? I haven't done either one in Excel. BTW I'm
running Excel 2003 beta if it makes a difference.

John

>.
>

[David McRitchie]

Hi John,
The referenced page for coding
http://www.mvps.org/dmcritchie/excel/code/sorttcp.txt
contains both macros and functions, your choice, each has
advantages.

Advantage of a macro. You only have one column, change it
and forget it, if you don't mind each node having 3 digits.
select cells to be processed
run the macro

Advantage of the function, you can type in and view your
TCP/IP addresses in the normal manner in one column and
sort on the other column with the function.
=IPSort(E20) -- if installed in same workbook
=personal.xls!IPSort(E20) -- if installed in personal.xls

There was a reference at the top of page
http://www.mvps.org/dmcritchie/excel/sorttcp.htm
to Getting Started with Macros and User Defined Functions (UDF)
http://www.mvps.org/dmcritchie/excel/getstarted.htm
but it was not highlighted with a yellow background as on other
pages. I've changed that now, and made a few changes
to the getstarted page to include some additional information on UDF..

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"John" <jdi...@hotmail.com> wrote in message news:03a301c35f08$f7c2ed30$a101...@phx.gbl...

[John]

Got it! I'm up and running using a user defined function.
Thanks for the help!

John

>.
>

[David McRitchie]

Hi John,
You're welcome. Nice to be informed which
choice you chose.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"John" <jdi...@hotmail.com> wrote in message news:072b01c36068$3e7053c0$a301...@phx.gbl...

[Myrna Larson]

Hi, David: I just looked at your code and see it's from 1999. If the user is running XL2000 or
better, you can make use of the Split and Join function, which simplifies the code a bit. The
speed is about the same as your code, but Ive added some error checking.

Option Explicit

Function IPSort(IPNum As String)
IPSort = FormatIPNum(IPNum, True)
End Function

Function IPNormal(IPNum As String) As Variant
IPNormal = FormatIPNum(IPNum, False)
End Function

Sub ConvertToIPSort()
ConvertIPNums Selection, True
End Sub

Sub ConvertToIPNormal()
ConvertIPNums Selection, False
End Sub

Private Sub ConvertIPNums(Rng As Range, PadIt As Boolean)
Dim c As Long
Dim r As Long
Dim v As Variant

v = Rng.Value
For r = 1 To UBound(v, 1)
For c = 1 To UBound(v, 2)
v(r, c) = FormatIPNum(CStr(v(r, c)), PadIt)
Next c
Next r
Rng.Value = v
End Sub

Private Function FormatIPNum(IPNum As String, PadIt As Boolean) As Variant
Dim i As Long
Dim n As Long
Dim Sections() As String
Dim Fmt As String
Const Dot As String = "."

On Error GoTo BadNumber

Sections() = Split(IPNum, Dot)
If UBound(Sections()) <> 3 Then Err.Raise 9 'must have 4 sections

If PadIt Then Fmt = "000"

For i = 0 To 3
n = CLng(Sections(i)) 'this will give an error if it's not a number
If n < 0 Or n > 255 Then
Err.Raise 9
ElseIf n < 100 Then
'only numbers < 100 need changing
Sections(i) = Format$(n, Fmt)
End If
Next i

FormatIPNum = Join(Sections(), Dot)
Exit Function

BadNumber:
FormatIPNum = CVErr(xlErrValue)
Exit Function
End Function