IP addresses in Excel 1

[tmt7734]

I'm trying to sort data by the list of ip addresses in an excel 2000 spreadsheet. Excel sorts it like .1, .10, .100, .11, etc.. Anyone kno how format the cells correctly for IP addresses in Excel 2000?

Thanks

 

[bluedragon2]

The easiest way I have found to sort IP addresses is to do a text to columns on them useing the . as the delimiter then sorting col 4 , 3 , 2 , 1. Then I fuse them back together.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Knew this would come in handy. Following is my initial flawed attempt at answering the same question previously, and then Harlan Grove's correction which should see you right. Use the formula in a helper column to turn the addresses into values and then sort on that column.


&quot;Harlan Grove&quot; <> wrote in message news:bJypa.1547$95.489@

http://www.newsranger.com...

&quot;Ken Wright&quot; wrote...
>Assuming your IP addresses start in A1 then put the following in B1 and
>copy down.
>
>=--SUBSTITUTE(A1,&quot;.&quot;,&quot;&quot
..

Unwise. IP addresses are 4 8-bit numbers (octets) concatenated with periods between them. They don't always have leading zeros, meaning your approach would only work if 1.1.1.1 always appeared as 001.001.001.001. If IP addresses could appear without leading zeros, then you need to convert it into the 32-bit unsigned integer that it actually represents. (Otherwise, 1.1.1.1 would appear
less than 0.0.1.100, which isn't the case.)

=INT(LEFT(A1,FIND(&quot;.&quot;,A1,FIND(&quot;.&quot;,A1)+1)-1))*2^24
+10*MOD(LEFT(A1,FIND(&quot;.&quot;,A1,FIND(&quot;.&quot;,A1)+1)-1),1)*2^16
+INT(MID(A1,FIND(&quot;.&quot;,A1,FIND(&quot;.&quot;,A1)+1)+1,256))*2^8
+10*MOD(MID(A1,FIND(&quot;.&quot;,A1,FIND(&quot;.&quot;,A1)+1)+1,256),1)

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[bluedragon2]

good one Ken, I never put that much thought into it (and I LOVE making formulas), GOOD one.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[tmt7734]

Thanks Ken,


I was working on similar formula developed a migraine.

 

[KenWright]

Some of Harlan's posts just blow me away - The guy is amazing.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------