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?
4 Answers
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:
Select your IP column and click
Data
>Text-to-Columns
Choose the Delimted option and click Next. Check the
Other
checkbox and enter a period.
. Click Next.Keep all of the columns, keep them as General, click the range icon to edit the
Destination
section.Select the columns where you want the new text to appear. Hit the Enter key.
Make sure your range is selected and click
Data
>Sort
. Enter the sorting criteria. Keep adding levels for each octet.Here's the end result:
-
@nixda Yea, slow poke :P Wish I could throw some rep your way as well Jul 16, 2013 at 19:29
-
Sorry, @nixda! Is it illegal for you to add your answer as well? Jul 16, 2013 at 19:31
-
Well, it worked... Time for you two to fight over the extra 15 rep points Jul 16, 2013 at 19:32
-
If one could extend this to include ranges and CIDR notation, eg: 192.162.0.0/24 that would be really helpful– tremorJun 2, 2021 at 20:38
I realize this is an old post, but in the interest of providing a working solution, I present the following.
Just put this formula in an adjacent cell and update the references to point to the cell containing your IP address (A1 in this example). This will produce a result similar to 010.121.008.030, which can then be sorted (correctly) alphabetically. Then set the new column's width to zero and voila. Time to enjoy a cup of coffee.
=TEXT(MID(A1,1,FIND(".",A1)),"000")&"."&TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-1-FIND(".",A1)),"000")&"."&TEXT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-1-FIND(".",A1,FIND(".",A1)+1)),"000")&"."&TEXT(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)),"000")
-
2Love this, perfect. A single formula, a "helper" single column. Thanks.– EarlsJan 20, 2015 at 16:16
-
As with all Excel formulas, you may need to use semicolons (
;
) instead of commas (,
) based on your system’s regional settings. Sep 21, 2017 at 6:00 -
1You could leave out the concatenated periods (
…&"."&…
) and get a plain, twelve-digit number, like 010121008030, which will also sort correctly (although the sort function might throw an annoying dialog box at you). Sep 21, 2017 at 6:06
In addition to John Homer's answer, I here provide a formula that also works in Apple's Numbers application, as TEXT
function does not exist there. Also tested in Google Docs.
=DEC2HEX(LEFT(B2,FIND(".",B2)-1),2)
&
DEC2HEX(MID(
B2,
FIND(".",B2)+1,
FIND(
".",
B2,
FIND(".",B2)+1
) - FIND(".",B2) - 1
),2)
&
DEC2HEX(MID(
B2,
FIND(
".",
B2,
FIND(".",B2)+1
) + 1,
FIND(
".",
B2,
FIND(
".",
B2,
FIND(".",B2)+1
)+1
) - FIND(
".",
B2,
FIND(".",B2)+1
) - 1
),2)
&
DEC2HEX(RIGHT(
B2,
LEN(B2) - FIND(
".",
B2,
FIND(
".",
B2,
FIND(".",B2)+1
)+1
)
),2)
-
1Awesome! Thanks for making it portable. Also works in LibreOffice and OpenOffice.– WolfFeb 24, 2020 at 13:15
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
-
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 Jul 17, 2013 at 5:19
-
@CanadianLuke You can also create a column BigIntIP. Then you can sort all the IP addresses and vice versa A-Z.– STTRJul 17, 2013 at 8:03
-
This works great! My only request would be that it reuses the old columns as printers are added Jul 18, 2013 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 ...– STTRJul 18, 2013 at 21:28