General discussion

  • Creator
    Topic
  • #2092619

    Sorting by IP address in Excel

    Locked

    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?

All Comments

  • Author
    Replies
    • #3869575

      Sorting by IP address in Excel

      by tclere ·

      In reply to Sorting by IP address in Excel

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

      [/code]

      • #3868766

        Sorting by IP address in Excel

        by rick ·

        In reply to Sorting by IP address in Excel

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

    • #3871271

      Sorting by IP address in Excel

      by mservino ·

      In reply to Sorting by IP address in Excel

      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

      • #3868769

        Sorting by IP address in Excel

        by rick ·

        In reply to Sorting by IP address in Excel

        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.

      • #3868767

        Sorting by IP address in Excel

        by rick ·

        In reply to Sorting by IP address in Excel

        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.

    • #3868758

      Sorting by IP address in Excel

      by mservino ·

      In reply to Sorting by IP address in Excel

      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.

      • #3870583

        Sorting by IP address in Excel

        by rick ·

        In reply to Sorting by IP address in Excel

        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

    • #3870582

      Sorting by IP address in Excel

      by rick ·

      In reply to Sorting by IP address in Excel

      This question was closed by the author

Viewing 3 reply threads