# Software

## 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?

• Author
Replies
• #3869575

### Sorting by IP address in Excel

by tclere ·

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
OrderCustom:=1,MatchCase:=False, Orientation:=xlTopToBottom
Range(“A1”).Select
End Sub

[/code]

• #3868766

### Sorting by IP address in Excel

by rick ·

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 ·

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 ·

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 ·

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 ·

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 ·

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

by rick ·