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

[Guest]

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

Thanks,
Jim

 

[CarlosAntenna]

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

 

[Haldun Alay]

Hi,

Following macro will help to get any octect of given IP no.
Usage is;
Assume IP No is in A1

=GetOctet(A1,3)


Function GetOctet(IpNo As String, WhichOctet As Integer) As String
If whichOctect > 4 Then
GetOctet = "Wrong Octet Number"
Exit Function
End If
Dim Octets
Octets = VBA.Split(IpNo, ".")
GetOctet = Octets(WhichOctet - 1)
End Function

Kind regards.
--
Haldun Alay
"JF" <[email protected]>, haber iletisinde ÅŸunları yazdı:[email protected]...
Trying to sort a list of IP addresses by the 3rd octet? Any Suggestions?

Thanks,
Jim

 

[Guest]

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.

 

[NHarkawat]

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

 

[Bernd]

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

 

[Harlan Grove]

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

 

[Frank Kabel]

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

 

[Harlan Grove]

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.

 

[Frank Kabel]

[...]

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