Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 29th 04, 02:17 PM
JF
 
Posts: n/a
Default 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

  #2   Report Post  
Old October 29th 04, 03:05 PM
CarlosAntenna
 
Posts: n/a
Default

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



  #3   Report Post  
Old October 29th 04, 03:08 PM
jf
 
Posts: n/a
Default

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

  #4   Report Post  
Old October 29th 04, 03:40 PM
NHarkawat
 
Posts: n/a
Default

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



  #5   Report Post  
Old October 29th 04, 04:15 PM
Bernd
 
Posts: n/a
Default

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


  #6   Report Post  
Old October 29th 04, 06:43 PM
Harlan Grove
 
Posts: n/a
Default

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



  #7   Report Post  
Old October 29th 04, 07:03 PM
Frank Kabel
 
Posts: n/a
Default

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

  #8   Report Post  
Old October 29th 04, 07:24 PM
Harlan Grove
 
Posts: n/a
Default

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


  #9   Report Post  
Old October 29th 04, 07:38 PM
Frank Kabel
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I sort email address, dedupes,check for errors in a docume. varun Excel Discussion (Misc queries) 2 January 14th 05 11:41 PM
Sort Problem jdb Excel Discussion (Misc queries) 1 January 10th 05 11:05 PM
"-" ignored in sort Mike H Excel Discussion (Misc queries) 8 January 2nd 05 07:48 AM
how create/maintain Excel "db" of client, output to Avery address. SunnyCritters Excel Discussion (Misc queries) 1 December 27th 04 09:09 PM
Data > Sort function amnesia? Dave D Excel Discussion (Misc queries) 1 November 29th 04 10:44 PM


All times are GMT +1. The time now is 09:31 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017