sorting IP addresses

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Milton Bliss - 10 Jul 2006 18:56 GMT

Does anyone have a good quick way to sort this list of IP addresses?
10.198.16.206
10.198.16.220
10.198.16.248
10.216.100.21
10.216.101.22
10.216.102.21
10.216.103.24
10.216.104.23
10.216.105.22
10.216.105.32
10.216.106.21
10.216.106.31
10.216.107.27
10.216.108.27
10.216.109.23
10.216.11.101
10.216.110.27
10.216.111.21
10.216.111.24
10.216.112.29
10.216.113.49
10.216.113.52
10.216.117.20

10.216.11.101 comes after 10.216.109.23 when Excel sorts the data. I
understand the problem is that these are not numbers, they are text.

I just don't know how to convert them. Any thoughts would be appreciated.

Reply to this Message

Larry Bud - 10 Jul 2006 19:32 GMT

> Does anyone have a good quick way to sort this list of IP addresses?
> 10.198.16.206
[quoted text clipped - 25 lines]
>
> I just don't know how to convert them. Any thoughts would be appreciated.

http://www.mvps.org/dmcritchie/excel/sorttcp.htm

Reply to this Message

Jim Cone - 11 Jul 2006 17:09 GMT

A commercial alternative from yours truly...
http://www.officeletter.com/blink/specialsort.html

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Milton Bliss" <milton.bliss@sunwestmanagement.com>
wrote in message
Does anyone have a good quick way to sort this list of IP addresses?
10.198.16.206
10.198.16.220
10.198.16.248
10.216.104.23
10.216.105.22
10.216.105.32
10.216.106.21
10.216.106.31
10.216.107.27
10.216.108.27
10.216.109.23
10.216.11.101
10.216.110.27
10.216.113.49
10.216.113.52
10.216.117.20

10.216.11.101 comes after 10.216.109.23 when Excel sorts the data. I
understand the problem is that these are not numbers, they are text.
I just don't know how to convert them. Any thoughts would be appreciated.

Reply to this Message

reybie - 11 Jul 2006 18:57 GMT

I just tried, convert text to columns, delimited by a "."
Then sorted column A, B, & C. I ran out of columns, but the result
looked promising :)

Signature

reybie

Reply to this Message

David McRitchie - 16 Jul 2006 03:04 GMT

Well I hope the original poster looks at my page that was already
suggested. http://www.mvps.org/dmcritchie/excel/sorttcp.htm
because that is the easiest way.

For what you are trying you would sort on columns B,C,D then
since Excel is not a sophisticated sort and maintains the order,
you can sort on Column A.

So if you had 8 columns to sort you would sort E,F,G,H then
the high order columns A,B,C,D

more informtion on sorting in
http://www.mvps.org/dmcritchie/excel/sorting.htm

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

> I just tried, convert text to columns, delimited by a "."
> Then sorted column A, B, & C. I ran out of columns, but the result
> looked promising :)

Reply to this Message

Mikeopolo - 16 Jul 2006 05:48 GMT

Just to show that using a formula IS possible, here's another way:

The following formula will turn 10.216.11.101 in to 10,216,011,101, and
the rows can then be sorted on this column. Sorry about the length of
the formula...:) . Assume the IP address is in A1, paste this to B1:

=(VALUE(LEFT(A1,FIND(".",A1)-1))*10^9)+(VALUE(LEFT(RIGHT(A1,LEN(A1)-FIND(".",A1)),FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))-1))*10^6)+VALUE(LEFT(RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))),FIND(".",RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))))-1))*10^3+VALUE(RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))))-FIND(".",RIGHT(RIGHT(A1,LEN(A1)-FIND(".",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1)))))))

Regards
Mike

Signature

Mikeopolo

Reply to this Message

Rate this thread: