How to Sort IP Address in Excel (6 Methods)

Excel treats an IP address as text. Therefore the Sort & Filter tool in Excel cannot sort IP addresses correctly. This article shows 6 different ways to sort IP addresses in Excel. The following picture illustrates how most of the methods work.

How to Sort IP Address in Excel (6 Easy Ways)

I am going to illustrate 6 easy ways to sort IP addresses in Excel for you. We are going to use the following dataset to highlight these methods. So, let’s jump in!

1. Sort IP Address Using Excel Formula

In this method, we are going to use a formula to convert the IP addresses so that they can be sorted correctly in Excel. To do this, follow the steps below.

Step 1: At first, enter the following formula in cell C5:

This formula finds dots(.) in cell B5 and, fills each octet number with zero/zeros if any of them contains less than three digits.

Then, copy this formula down to the cells below using the Fill Handle tool. This will fill all the IP addresses with zeros as the first one.

Step 2: After that, select all the converted IP addresses.

Step 3: Then sort them using the Sort & Filter tool from the Home tab. You can also right-click on the selected cells and sort them from there.

Step 4: Expand the selection while sorting as follows.

Now both of the converted IPs and the original IPs are sorted as shown below.

An Alternative Formula:

Step 5: Using the following formula also gives the same result.

But this formula converts the IPs to decimal numbers as opposed to filling them with zeros in the earlier one. You can sort the IPs the same way we have sorted them earlier.

Read More: How to Do Advanced Sorting in Excel

2. Sort IP Address by Text to Columns Wizard

An alternative way to sort IP addresses is to use the Text to Columns wizard in Excel. Go through the following steps to be able to do that.

Step 1: Firstly, select all the IPs as follows. Keep 4 adjacent cells empty to the right.

Step 2: Next, click on the Text to Columns icon from the Data tab as shown in the following picture.

Step 3: After that, mark your data type as Delimited and then click on Next.

Step 4: Now check the Other tab and type a dot(.) in the textbox. Then hit the Next button.

Step 5: After that, keep the data format general. Then choose the destination as to $C$5. You can do this by clicking on the small upward arrow on the right side of the destination field box. Then select cell C5. And finally hit the Finish button.

But if the adjacent cells aren’t empty, you have to replace them by clicking OK.

Step 6: Now the IPs are divided into 4 octets. Keep the entire data range selected as shown.

Step 7: Now, you need to perform custom sorting on them using the Sort & Filter tool.

Step 8: First sort them by column C. Then add new levels and sort them by column D, E and F respectively. Now, if you hit the OK button, the IPs will be sorted.

You can hide or delete the octets if you want.

Read More: How to Perform Custom Sort in Excel

3. Arrange IP Address in Excel Table

IP addresses can be sorted in Excel Table using another formula. Follow the steps below to apply this method.

Step 1: At first, create an Excel Table using the dataset as shown below.

Step 2: Click anywhere on the ‘Convert IP’ cells. Then apply the following formula in this table:

This will fill all the IPs with zeros as done earlier.

Step 3: Now, sort the converted IPs as done in the previous methods.

Finally, the IP addresses are sorted.

Read More: How to Perform Random Sort in Excel

4. Sort IP Address with Flash Fill in Excel

If the first three octets of your dataset are the same, then you can use the Flash Fill in Excel to sort them. Perhaps this is the quickest and easiest method to sort IP addresses in Excel. The steps for this method are discussed below.

Step 1: Type the last octet digits of the first IP in cell C5. Now if you do the same for the second IP, you will see a grey-colored list as follows. These are the last octets of the IPs.Advantages of Sorting Data in Excel

Step 2: Now hit Enter and the list will be filled. Select the entire list and sort them.

Don’t forget to expand the selection while sorting.

Now the IP addresses are sorted as follows.

Read More: How to Sort and Filter Data in Excel

5. Sort IP Address Using User Defined Function (UDF)

Another amazing way to sort IP addresses is by using User Defined Functions(UDF) in Excel. To do so, go through the steps below.

Step 1: At first, open the Microsoft Visual Basic for Applications(VBA) window. The keyboard shortcut is ALT+F11 in Windows and Opt+F11 in Mac. You can also do that from the Developer tab. If it is not visible, go to File>>Options>>Customized Ribbon>>Main Tabs and check the checkbox for Developer and then hit OK.

Step 2: From the Insert tab, select Module.

Step 3: Now, copy the following code and paste it into the blank field.

Function SortIP(IP As String) As String
    Dim FirstDot As Integer
    Dim SecondDot As Integer
    Dim ThirdDot As Integer
    Dim FirstOctet As String
    Dim SecondOctet As String
    Dim ThirdOctet As String
    Dim FourthOctet As String
    FirstDot = InStr(1, IP, ".", vbTextCompare)
    SecondDot = InStr(FirstDot + 1, IP, ".", vbTextCompare)
    ThirdDot = InStr(SecondDot + 1, IP, ".", vbTextCompare)
    FirstOctet = Left(IP, FirstDot - 1)
    SecondOctet = Mid(IP, FirstDot + 1, SecondDot - FirstDot - 1)
    ThirdOctet = Mid(IP, SecondDot + 1, ThirdDot - SecondDot - 1)
    FourthOctet = Mid(IP, ThirdDot + 1, Len(IP))

    SortIP = Right("000" & FirstOctet, 3) & "."
    SortIP = SortIP & Right("000" & SecondOctet, 3) & "."
    SortIP = SortIP & Right("000" & ThirdOctet, 3) & "."
    SortIP = SortIP & Right("000" & FourthOctet, 3)
End Function

Step 4: Then close it from the File tab and return to Excel.

Step 5: Now, type the following formula in cell C5:

Step 6: You can see the IP is filled with zeros. After that, copy the formula to the cells below. Sort those converted IPs following the same procedures in the earlier methods.

Finally, all the IPs are sorted as shown below.

Read More: Advantages of Sorting Data in Excel

6. Arrange IP Address with VBA in Excel

There is another way to sort IPs using VBA. This method consists of the following steps.

Step 1: Select the cells containing the IP addresses.

Step 2: Then open the VBA window and insert a module as in the earlier method. Then copy the following code and paste it on the blank window.

Sub ConvertIP()
    Dim xReg As New RegExp
    Dim xMatchs As MatchCollection
    Dim xMatch As Match
    Dim xRng As Range
    Dim xCellRange As Range
    Dim I As Long
    Dim xConv() As String
    On Error Resume Next
    Set xRng = Application.InputBox("Select cell/Range:", "Convert IP Address", Selection.Address, , , , , 8)
    If xRng Is Nothing Then Exit Sub
    With xReg
        .Global = True
        .Pattern = "\d{1,3}.+\d{1,3}.+\d{1,3}.+\d{1,3}"
        For Each xCellRange In xRng
            Set xMatchs = .Execute(xCellRange.Value)
            If xMatchs.Count = 0 Then GoTo xPause
            For Each xMatch In xMatchs
                xConv = Split(xMatch, ".")
                For I = 0 To UBound(xConv)
                    xConv(I) = Right("000" & xConv(I), 3)
                    If I <> UBound(xConv) Then
                        xConv(I) = xConv(I) & "."
                    End If
                Next
            Next
            xCellRange.Value = Join(xConv, "")
xPause:
        Next
    End With
End Sub

Step 3: Now, from the Tools tab, select References. This will open a new dialog box.

Step 4: Scroll down and check Microsoft VBScript Regular Expressions 5.5 from the list of Available References. Then hit OK.

Step 5: Now, press F5. This will ask for the cell range. You can either type the cell range or toggle back to excel and select the entire cell range. As we have selected the entire range in step 1, Excel automatically takes that as input. Finally hit the OK button.

Note: Do not minimize the VBA window when you are pressing F5.

You can see the IP addresses are filled with zeros. Now you can sort them as easily as before.

Read More: Difference Between Sort and Filter in Excel

Things to Remember

• Method 4 only works if 3 of the 4 octets of the IP address have the same digits.
• Direct use of the Sort & Filter tool may give the correct result for this dataset. Only because three octets of the IPs are the same.

Download Practice Workbook

You can download the practice workbook using the download button below.

Conclusion

Now you know 6 different ways to sort IP addresses in Excel. Which one do you prefer the most? Do you know any other easy methods to sort IP addresses in Excel? Please let us know in the comment section. You can ask for further queries there too.

Related Articles

• How to Sort Drop Down List in Excel
• How to Sort Excel Tabs in Ascending or Descending Order

<< Go Back to Sort in Excel | Learn Excel