• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help!! Breaking data within a cell into separate components

O

outlaw

New Member
I have data in a column (an IP address)that I need to sort by individual octets. For example, the last octet (xxx.xxx.xxx.999)is what I want to sort by. I would like to be able to take the IP address and break it into individual octets to be placed in their own columns or somehow separate the 4th octet from the others. Thank you in advance.
 
Hi, outlaw!


Give a look to this file:

https://dl.dropbox.com/u/60558749/Help%21%21%20Breaking%20data%20within%20a%20cell%20into%20separate%20components%20%28for%20outlaw%20at%20chandoo.org%29.xlsx


It shows 3 methods:

a) Row 1, 3 helper columns and different -but easiest- formulas for each chunk

b) Row 2, 4 helper columns and same -but not so easy- formula for each chunk

c) Row 3, no helper columns and same -but nothing easy- formula for each chunk


If you ask me, of course 3rd. one, but I think it'd be safer and less brain cracking to stick with 2nd. one.


Regards!
 
Hi, outlaw!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: Night, here's GMT-3. And the same for you too.
 
Hi Outlaw,


You can make SirJB7's formula more worse, by reading the following blog.


http://chentiangemalc.wordpress.com/2011/02/03/geeky-excel-formulas-subnet-math-group-by-subnet-using-built-in-excel-2010-formulas/


@SirJB7,

a Silly request.. Can you please change your example's IP address's last subnet to something else :)


Regards,

Deb
 
@Debraj Roy

Hi!

You ask for it, you get it...

Download again the file from same link to find added the IP addresses of your link.

Regards!

PS: And why did he want me to do that? Who knows...

PS2: Worse... what an awful adjective for a beautiful formula...

PS3: And I left the two previous examples so as to understand the evolution...
 
Hi Outlaw,


Can you please try the below for me..


=MID("."&$A1&".",SMALL(IF(ISERROR(SEARCH(MID("."&$A1&".",ROW($1:$99),1),".")) ,"",ROW($1:$99)+1),COLUMN(A1)),SMALL(IF(ISERROR(SEARCH(MID("."&$A1&".",ROW($1:$99),1),".")),"",ROW($1:$99)) ,COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID("."&$A1&".",ROW($1:$99),1),".")),"",ROW($1:$99)),COLUMN(A1))-1)


Please confirm the Formula by using Ctrl+Shift+Enter, not just Enter.


https://dl.dropbox.com/u/78831150/Excel/Help%21%21%20Breaking%20data%20within%20a%20cell%20into%20separate%20components%20%28for%20outlaw%20at%20chandoo.org%29.xlsx


@ SirJB007,


Just to clarify, I was talking about 123.45.67.890 , when I tried to get the class of IP address (via one of my UDF).. I got error only due to 890.

As you also know, there is no number beyond 2^15-1, in the same rule, No IP beyond.. 2^8-1..


PS: I hope now I have made your formula better, by providing more worse Formula.. :)
 
@Debraj Roy

Hi!

Gotcha, touché and ooops... altogether...

I didn't notice the invalid IP address, first I tried with 123.123.123.123 but I thought it might have been not so exemplary, so I changed to normal sequences with different number of digits.

And yes, I know that no IP beyond 2^8-1, but what are rules for if not for breaking them? My preferred activity, I should say.

Regards!

PS: those of row 3 are smart and nice and beautiful and lovely formulas :p
 
Hi Sir,


Thanks a lot..

We can use the same formula for "Text To Column" using Delimiter.. upto 99 Delimiters..


Regards,

Deb
 
You must log in or register to reply here.
Back
Top