How do I sort IP addresses is ascending numerical order?

[Rob Covey]

I have been pulling my hair out trying to sort a list of ip
addresses in ascending numerical order. The following is
what happens when I try to sort it.

Eg.

1.1.1.1
1.1.1.251
1.1.1.3
1.1.1.5
1.1.1.61

The result that I am hoping for is;

1.1.1.1
1.1.1.3
1.1.1.5
1.1.1.61
1.1.1.251

Any help would be appreciated

Thanks and regards

Rob

[Bill Manville]

In article <2d2d01c1050c$e3849d30$9ae62ecf@tkmsftngxa02>, Rob Covey wrote:
> The result that I am hoping for is;
>
> 1.1.1.1
> 1.1.1.3
> 1.1.1.5
> 1.1.1.61
> 1.1.1.251
>

I guess you have to create a formula in a parallel column that gives a result
that will sort correctly,
e.g. giving (((1*1000)+1)*1000)+1)*1000+251 = 1001001251

Now for the tricky bit - making the formula.
Whilst it would be possible to do it using what John Walkenbach calls a
megaformula, I think it would be easier to use a VBA function.

Function SortIPCode(IPCode As String) As Double
Dim iDot As Integer
Dim stLeft As String
Dim dResult As Double
stLeft = IPCode
iDot = InStr(stLeft, ".")
Do Until iDot = 0
dResult = dResult * 1000 + Int(Left(stLeft, iDot - 1))
stLeft = Mid(stLeft, iDot + 1)
iDot = InStr(stLeft, ".")
Loop
SortIPCode = dResult * 1000 + Int(stLeft)
End Function

Put this in a standard module.
In your worksheet, if your IP addresses are in A2:A100, and column B is not
used, put this formula in B2
=SortIPCode(A2)
and fill it down to B100

Then sort on column B.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

[Dana DeLouis]

One of the other ways to do this is to use <Data><Text to Columns> and split
the data based on the period "."
Sort on columns 4,3,2, then 1.
Then delete these extra columns.

--
HTH
Dana DeLouis Windows Me & Office XP

"Rob Covey" <rober...@hotmail.com> wrote in message
news:2d2d01c1050c$e3849d30$9ae62ecf@tkmsftngxa02...

[David McRitchie]

I think Bill Manville provided an adequate solution with a Function.
I prefer to use a subroutine myself so I see the column only once
in whichever format I want to see it. Also as only one column you
only have to sort one column not four columns whether using
subroutine (mine) or function (Bill) My solutions are in
http://www.geocities.com/davemcritchie/excel/sorttcp.htm
for the description, and the code is in
http://www.geocities.com/davemcritchie/excel/code/sorttcp.txt

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Dana DeLouis" <ng_...@hotmail.com> wrote in message news:e$K5fBfBBHA.1188@tkmsftngp05...

[jir...@hornblower.com]

Amplifying Dana DeLouis' solution...The following method works great. You need 6 columns, but can sort on only one.

Let's say A2 contains the IPv4 address in normal non-zero-filled format like "192.168.15.42". In B2 enter the formula:
=split(A2,".")

which puts the four numeric address terms in columns B2, C2, D2 and E2. Then in F2 enter
=4294967296*B2+65536*C2+256*D2+E2

For example, IP 192.168.15.42 generates an "IP sort" number 824644734762.

Copy the two formulas down their respective columns, call column E "IPsort" or some such and sort ascending on just this column.