How Do I sort an IP address by the 3rd Octet?
 

Trying to sort a list of IP addresses by the 3rd octet? Any Suggestions?

Thanks,
Jim

Use a helper column and the MID function to extract the third octet, sort by
that column, then delete the helper if you wish.

Carlos

"JF" wrote in message
...
Trying to sort a list of IP addresses by the 3rd octet? Any Suggestions?

Thanks,
Jim

For example I have:
172.21.34.82
172.21.48.80
172.21.48.83
172.22.42.100
172.22.42.101
172.24.10.15
172.24.10.16
172.24.102.12
172.24.14.15

And I want to sort this list by the third group of numbers.


"JF" wrote:

Trying to sort a list of IP addresses by the 3rd octet? Any Suggestions?

Thanks,
Jim

Assuming the 3rd group of numbers always start at character # 8th use the
following in a helper column
=MID(A1,8,SEARCH(".",A1,8)-8)
Then sort it based on the helper column
Nitin
"jf" wrote in message
...
For example I have:
172.21.34.82
172.21.48.80
172.21.48.83
172.22.42.100
172.22.42.101
172.24.10.15
172.24.10.16
172.24.102.12
172.24.14.15

And I want to sort this list by the third group of numbers.


"JF" wrote:

Trying to sort a list of IP addresses by the 3rd octet? Any
Suggestions?

Thanks,
Jim

Get my macro ch2str (sort_chapter.xls) from
http://www.bplumhoff.de/html/software.html

Create a helper column with =RIGHT(A1,10) and sort by this
column.

Your result will be sorted by 3. octet first then 4. octet.

Regards,
Bernd

"jf" wrote...
For example I have:
172.21.34.82
172.21.48.80
172.21.48.83
172.22.42.100
172.22.42.101
172.24.10.15
172.24.10.16
172.24.102.12
172.24.14.15
....

As those with a very slight understanding of what IP addresses are might
have noticed from this sample (and should have known before responding),
octets should be treated as numbers. Therefore, the 2nd to last IP address
with 3rd octet 102 should be sorted last (in ascending order), not 3rd,
which it would be with simplistic text parsing.

What you need is to pull the 3rd octets into a separate column AS NUMBERS,
and sort on that column. The general way to isolate the 3rd octet in x is
with a formula like

=INT(--MID(x,FIND(CHAR(127),SUBSTITUTE(x,".",CHAR(127),2) )+1,3))

Harlan Grove wrote:
[...]
As those with a very slight understanding of what IP addresses are
might have noticed from this sample (and should have known before
responding), octets should be treated as numbers. Therefore, the 2nd
to last IP address with 3rd octet 102 should be sorted last (in
ascending order), not 3rd, which it would be with simplistic text
parsing.

What you need is to pull the 3rd octets into a separate column AS
NUMBERS, and sort on that column. The general way to isolate the 3rd
octet in x is with a formula like

=INT(--MID(x,FIND(CHAR(127),SUBSTITUTE(x,".",CHAR(127),2) )+1,3))

Hi Harlan
though I like your solution idea this may not work in non-English
countries. e.g. for the following IP address
172.24.1.12

your formula returns a date value ("1-Jan-2004") if the dot is used for
delimiting the date parts.

Frank

"Frank Kabel" wrote...
....
=INT(--MID(x,FIND(CHAR(127),SUBSTITUTE(x,".",CHAR(127),2) )+1,3))

though I like your solution idea this may not work in non-English
countries. e.g. for the following IP address
172.24.1.12

your formula returns a date value ("1-Jan-2004") if the dot is used for
delimiting the date parts.

Part of me wants to respond that continental Europe has lost whatever
contest there may have been to establish computer and IP standards, and the
sooner y'all recognize that '.' is the decimal point and ',' the thousands
separator, and '/' or '-' date component separators, the better for all.

However, I'll address your point. If you live in some backwards region that
doesn't use US-standard settings by default, and you need to work with data
using US-standard separators (such as the .s in IP addresses), then change
those US-standard separators to your local separators as the initial step.

=INT(--MID(SUBSTITUTE(x,".",","),
FIND(CHAR(127),SUBSTITUTE(x,".",CHAR(127),2))+1,3) )

Or download and install Laurent Longre's MOREFUNC.XLL add-in, available at

http://longre.free.fr/english/

and use it's WMID function.

[...]
though I like your solution idea this may not work in non-English
countries. e.g. for the following IP address
172.24.1.12

your formula returns a date value ("1-Jan-2004") if the dot is used
for delimiting the date parts.

Part of me wants to respond that continental Europe has lost whatever
contest there may have been to establish computer and IP standards,
and the sooner y'all recognize that '.' is the decimal point and ','
the thousands separator, and '/' or '-' date component separators,
the better for all.

lol
probably the same chance that the 'Americans' will all adapt to the
metric system and forget about feet, inches, gallons, etc :-)


However, I'll address your point. If you live in some backwards
region that doesn't use US-standard settings by default, and you need
to work with data using US-standard separators (such as the .s in IP
addresses), then change those US-standard separators to your local
separators as the initial step.

Now part of me wants to respond that 'old Europe' is in several areas
more advanced than the US but this is definetely not the right place to
discuss this and both of us would find enough examples to verify our
points :-)


=INT(--MID(SUBSTITUTE(x,".",","),
FIND(CHAR(127),SUBSTITUTE(x,".",CHAR(127),2))+1,3) )

just a final comment about the working formula. I think it works also
without using '--' as INT will do this conversion anyway.

Frank