General discussion

Locked

Sorting by IP address in Excel

By rick ·
I'm trying to sort a column of ip addresses in ascending order using Excel 97. The problem is that excel recognizes, for example, 10.252.16.120 as being lower in value than 10.252.16.22 and therefore the 16.120 address would be listed before the 16.22 address when doing an ascending sort since excel is interperating the "." as a decimal. I would want the 16.22 to be listed before the 16.120 when I do an ascending sort. Is there a way to program excel to sort the column based on the last octet of the ip address if the first three octets in the column are the same? What if the first three octets vary?

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Comments

Collapse -

Sorting by IP address in Excel

by tclere In reply to Sorting by IP address in ...

You can add this code to your workbook. It will put all of the nonessential zeros back into your Octets. Then you can sort it and get the results you are looking for. This sample assumes that the IP addresses are in column A and there is no other data. You can change the code to suit your needs.


Option Explicit
Option Base 1

Public Sub FixData()
    Dim sTmp As String, iRow As Long, iPos As Integer
    Dim i(4) As Integer
    iRow = 1
    Do While Len(Range("A" & iRow).Text) >0
        sTmp = Range("A" & iRow).Text
        'Get the first Octet
        iPos = InStr(1, sTmp, ".")
        i(1) = CInt(Left(sTmp, iPos - 1))
        sTmp = Right(sTmp, Len(sTmp) - iPos)
        'Get the second Octet
        iPos = InStr(1, sTmp, ".")
        i(2) = CInt(Left(sTmp, iPos - 1))
        sTmp = Right(sTmp, Len(sTmp) - iPos)
        'Get the third Octet
        iPos = InStr(1, sTmp, ".")
        i(3) = CInt(Left(sTmp, iPos - 1))
        sTmp = Right(sTmp, Len(sTmp) - iPos)
        'Get the last Octet
        i(4) = CInt(sTmp)
        'Rewrite the string with all of the zeros
        sTmp = Format(i(1), "000") & "." & _
               Format(i(2), "000") & "." & _
               Format(i(3), "000") & "." &_
               Format(i(4), "000")
        Range("A" & iRow).FormulaR1C1 = sTmp
        iRow = iRow + 1
    Loop
    Columns("A:A").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1,MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
End Sub

Collapse -

Sorting by IP address in Excel

by rick In reply to Sorting by IP address in ...

Since I am somewhat of a novice in Excel, could you tell how to go about adding this code into my workbook? Thanks.

Collapse -

Sorting by IP address in Excel

by MServino In reply to Sorting by IP address in ...

another thing you can do, without all the code is to just insert 4 columns on your worksheet, copy from your IP list into the first new column, use DATA --> Text to Columns, expand on a delimiter of ".". Then just select the 4 columns Format Cells,as numbers, General, then do a sort on the columns. End of story, if you don't want to see the columns your sorting on just hide them.


-Mike

Collapse -

Sorting by IP address in Excel

by rick In reply to Sorting by IP address in ...

I tried this and had problems with the 4th column (last octet)sorting correctly. I also would want to display the "." in the ip address and sort the rest of the data in the spreadsheet at the same time. Thanks for your suggestion.

Collapse -

Sorting by IP address in Excel

by rick In reply to Sorting by IP address in ...

I tried this and had problems with the 4th column (last octet)sorting correctly. I also would want to display the "." in the ip address and sort the rest of the data in the spreadsheet at the same time. Thanks for your suggestion.

Collapse -

Sorting by IP address in Excel

by MServino In reply to Sorting by IP address in ...

how many subnets are you using? does your first octet vary if not you only need to sort on the 2nd, 3rd, and 4th octets. if it does you'll more than like find that your 1st octet and 2nd octet will always go together. For example if you have an internal IP of 10.1.x.x and you may have externals of 206.26.x.x in which case you can just sort on the 1st 3rd and 4th octets.

Collapse -

Sorting by IP address in Excel

by rick In reply to Sorting by IP address in ...

Thanks for your help. I realized that I was sorting on the 4th octet only. Once I sorted on 3 of the octets I got the results I was looking for.
-Rick

Collapse -

Sorting by IP address in Excel

by rick In reply to Sorting by IP address in ...

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums