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.

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.

 

[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.

 

[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

 

[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

 

[John]

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

John

-----Original Message-----
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..

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

-----Original Message-----
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

changed
Nov. 2001]

My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Knew this would come in handy.


.

.

 

[David McRitchie]

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