How to Sort an IP Address in Excel – 6 Methods

Excel treats an IP address as text.

This is an overview:

This is the sample dataset.

Method 1 – Sort the IP Address Using an Excel Formula

Step 1:

Enter the following formula in C5:

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

• Drag down the Fill Handle tool. This will fill all the IP addresses with zeros.

Step 2:

• Select all the converted IP addresses.

Step 3:

• Select Sort & Filter in the Home tab to sort the cells. You can also right-click the selected cells and sort them.

Step 4:

• Expand the selection while sorting as shown below.

Both the converted IPs and the original IPs are sorted.

An Alternative Formula:

Step 5:

• Use the following formula:

It converts the IPs to decimal numbers.

Read More: How to Do Advanced Sorting in Excel

Method 2 – Sort the IP Address using the Text to Columns Wizard

Step 1:

• Select all the IPs. Keep 4 adjacent cells empty to the right.

Step 2:

• Click Text to Columns in the Data tab.

Step 3:

• Check Delimited and click Next.

Step 4:

• Check Other and enter a dot(.) in the textbox. Click Next.

Step 5:

• Keep the data format general.
• Choose the destination as $C$5 (click the small upward arrow on the right side of the destination field box).
• Select C5.
• Click Finish.

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

Step 6:

• The IPs are divided into 4 octets. Keep the entire data range selected.

Step 7:

• Perform custom sorting, using Sort & Filter.

Step 8:

• Sort cells by column C.
• Add new levels and sort them by column D, E and F.
• Click OK.

• Hide or delete the octets.

Read More: How to Perform Custom Sort in Excel

Method 3 – Arrange the IP Address in an Excel Table

Step 1:

• Create an Excel Table using the dataset below.

Step 2:

• Click a cell in ‘Convert IP’.
• Use the following formula in this table:

It will fill all IPs with zeros.

Step 3:

• Sort the converted IPs as described in the previous methods.

The IP addresses are sorted.

Read More: How to Perform Random Sort in Excel

Method 4 – Sort IP Addresses with the Flash Fill in Excel

Step 1:

• Enter the last octet digits of the first IP in C5.
• If you do the same for the second IP, you will see a gray-colored list: these are the last octets of the IPs.

Step 2:

• Press Enter and the list will be filled.
• Select the entire list and sort it.

• Expand the selection while sorting.

The IP addresses are sorted.

Read More: How to Sort and Filter Data in Excel

Method 5 – Sort the IP Address Using a User Defined Function (UDF)

Step 1:

• Open the Microsoft Visual Basic for Applications(VBA) window. Press ALT+F11 in Windows or Opt+F11 in Mac. You can also go the Developer tab. If it is not visible, go to File>>Options>>Customized Ribbon>>Main Tabs ,check Developer and click OK.

Step 2:

• In the Insert tab, select Module.

Step 3:

• Enter the following code.

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:

• Close the window.

Step 5:

• Enter the following formula in C5:

Step 6:

The IP is filled with zeros.

• Drag down the Fill Handle to see the result in the rest of the cells.

The IPs are sorted.

Read More: Advantages of Sorting Data in Excel

Method 6 – Arrange the IP Address with VBA in Excel

Step 1:

• Select the cells containing the IP addresses.

Step 2:

• Open the VBA window and insert a module as in Method 5.
• Enter the following code.

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:

• In Tools, select References.

Step 4:

• Scroll down and check Microsoft VBScript Regular Expressions 5.5 in Available References.
• Click OK.

Step 5:

• Press F5. Enter the cell range or toggle back to excel and select the entire cell range. As the entire range was selected in step 1, Excel automatically takes it as input.
• Click OK.

Note: Do not minimize the VBA window while pressing F5.

The IP addresses are filled with zeros. Sort them as described 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.

Download Practice Workbook

Download the practice workbook.

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