Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a fairly large (over 200 row) Excel spreadsheet that lists items in my network (i.e. printers, servers, workstations, network projectors, etc), and one of the first columns is the IP Address field, which takes the form 192.168.x.y. My issue when I try to sort it is that it goes (as an example) from 192.168.0.85 to 192.168.0.9. What I would like to see is it sorting based on the first 3 octets, then the last octet logically (i.e. .1, .2, .3, etc). Is this possible? If so, how?

share|improve this question
    
Are helper columns a valid option? –  nixda Jul 16 '13 at 18:34
    
If it fixes the issue, then sure... But I'd feel pretty sad if I had to rewrite the entire table by hand :-(. Don't let that discourage a solution that will work, though –  Canadian Luke Jul 16 '13 at 18:37

2 Answers 2

up vote 17 down vote accepted

As nixda mentioned in the comments, helper columns will make this possible. You have two options for maintaining the sheet afterward:

  • Add all new IPs in the split helper columns.
  • Repeat the Text-to-columns procedure for new additions.

Here's the procedure:

  1. Select your IP column and click Data > Text-to-Columns text-to-columns

  2. Choose the Delimted option and click Next. Check the Other checkbox and enter a period .. Click Next. choose . as delimiter

  3. Keep all of the columns, keep them as General, click the range icon to edit the Destination section. change dest 1

  4. Select the columns where you want the new text to appear. Hit the Enter key. change dest 2

  5. Make sure your range is selected and click Data > Sort. Enter the sorting criteria. Keep adding levels for each octet. sorting

  6. Here's the end result: result

share|improve this answer
2  
Was writing the same. I guess I need to be faster. +1 anyway :) –  nixda Jul 16 '13 at 19:28
    
@nixda Yea, slow poke :P Wish I could throw some rep your way as well –  Canadian Luke Jul 16 '13 at 19:29
    
Sorry, @nixda! Is it illegal for you to add your answer as well? –  rishimaharaj Jul 16 '13 at 19:31
    
Well, it worked... Time for you two to fight over the extra 15 rep points –  Canadian Luke Jul 16 '13 at 19:32
Sub IPSplit()

HeaderRow = 1
ColimnName = "A"
BeginIPaddsressData = 2

Dim HeaderArray As Variant
HeaderArray = Array("IP oct 1", "IP oct 2", "IP oct 3", "IP oct 4")

Dim Octet() As String
Dim RangeSearch As Range, RangeFound As Range, LastCell As Range
Dim LastCellRowNumber As Long, LastCellColumnNumber As Long, RowNumber As Long

With ActiveSheet
Set LastCell = .Cells(HeaderRow, .Columns.Count).End(xlToLeft)
LastHeaderColumnNumber = LastCell.Column

Set RangeSearch = Range("1:1")
Set RangeFound = RangeSearch.Find(What:=HeaderArray(0), LookIn:=xlValues)

If RangeFound Is Nothing Then
RowNumber = 2
    If .Cells(RowNumber, .Columns.Count) <> vbNullString Then
        Set LastCell = .Cells(RowNumber, .Columns.Count)
        LastCellColumnNumber = LastCell.Column
    Else
        Set LastCell = .Cells(RowNumber, .Columns.Count).End(xlToLeft)
'Specifies the last column LastCellColumnNumber.

        LastCellColumnNumber = LastCell.Column
    End If

Range(Cells(HeaderRow, LastCellColumnNumber + 1), Cells(HeaderRow, LastCellColumnNumber + 4)).Value = HeaderArray
'Insert Header

Else
LastCellColumnNumber = RangeFound.Column - 1
End If

Set LastCell = .Cells(.Rows.Count, ColimnName).End(xlUp)
'Specifies the last cell number in the column ColimnName.

LastCellRowNumber = LastCell.Row   
End With

    For I = BeginIPaddsressData To LastCellRowNumber

    Octet = Split(Cells(I, ColimnName).Value, ".")
    For O = 0 To 3       
'cells populate the values of octets 1-4.
      If (UBound(Octet) - O) >= 0 Then      
         Cells(I, ColimnName).Offset(0, LastCellColumnNumber + O).Value = Octet(O)
      End If
    Next
Next
End Sub
share|improve this answer
    
I can't test that right now (not at work), but I will try it when I get to work. This looks like it would be more adaptable to longer lists, especially if I just add a 'Resort' button to run this macro –  Canadian Luke Jul 17 '13 at 5:19
    
@CanadianLuke You can also create a column BigIntIP. Then you can sort all the IP addresses and vice versa A-Z. –  STTR Jul 17 '13 at 8:03
    
This works great! My only request would be that it reuses the old columns as printers are added –  Canadian Luke Jul 18 '13 at 19:20
    
@CanadianLuke If you rename a subject "IP oct 1" then the column will be added to the right. And the old remain. This is if you need a story. For example, when migrating from one network to another range. Maybe I did not quite understand the question ... –  STTR Jul 18 '13 at 21:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.