sorting IP addresses

M

Milton Bliss

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

Larry Bud

Milton said:
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.
Click to expand...

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

Jim Cone

A commercial alternative from yours truly...
http://www.officeletter.com/blink/specialsort.html
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Milton Bliss" <[email protected]>
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.
 
R

reybie

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

David McRitchie

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
 
M

Mikeopolo

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
 
Top