This post will guide you how to sort IP address in your worksheet. How do I sort IP address with an excel formula. How to sort IP address with Text To Columns feature in excel.

1. Sort IP Address with Formula

Assuming that you have an IP list of data in the range of cells B1:B5, and you want to sort those IP list from low to high, how to achieve the result with excel formula. You need to create a formula based on the Text Function, the LEFT function, the FIND function and the MID function, the RIGHT function. Just like this:

=TEXT(LEFT(B1,FIND(".",B1,1)-1),"000") & "." & TEXT(MID(B1,FIND(
".",B1,1)+1,FIND(".",B1,FIND(".",B1,1)+1)-FIND(".",B1,1)-1),"000")
& "." & TEXT(MID(B1,FIND(".",B1,FIND(".",B1,1)+1)+1,FIND(".",B1,
FIND(".",B1,FIND(".",B1,1)+1)+1)-FIND(".",B1,FIND(".",B1,1)+1)-1),
"000") & "." & TEXT(RIGHT(B1,LEN(B1)-FIND(".",B1,FIND(".",B1,FIND(
".",B1,1)+1)+1)),"000")

You will see this formula is quite long, but it is a single one formula and let’s see the detailed steps:

#1 type this formula into the formula box of Cell C1, then press Enter key in your keyboard, and drag the AutoFill Handler over other cells to apply this formula to sort other IP Address.

#2 keep the newly IP Address selected, and go to DATA tab, click Sort A to Z under Sort&Filter group.

#3 select Expand the selection radio button, and then click Sort button.

#4 you will see the IP addresses have been sorted.

2. Sort IP Address with Text to Columns

You can also use the Text to Columns feature to sort IP address in excel, You can use this feature to divide the IP Addresses into separate columns and putting each octet in its own column. Before doing those actions, you need to make sure that there are three blank columns next to the right of your IP address. So just do the following steps:

#1 Select the cells that contain IP addresses

#2 go to DATA tab, click Text to Columns command under Data Tools group. The Convert Text to Columns Wizard dialog will appear.

#3 Checked Delimited option, and click Next button.

#4 select the checkbox next to Other: box and then type . into the text box, and click Next button.

#5 select one destination cell to paste the result ( C1). Then click Finsh button.

#6 select all cells that contain IP addresses.

#7 click Sort command under Sort&Filter group. The Sort dialog will appear.

#8 clicking Add Level to sort data from column C to F.

#9 click Ok button. You will see that the IP addresses have been sorted.

3. Sort IP Address with VBA Code

let’s delve into the third method, leveraging the power of VBA to automate the sorting of IP addresses in Excel with the ability to select a range and choose a destination cell.

Press Alt + F11 to open the VBA editor.

In the VBA editor, click on Insert in the menu and choose Module to add a new module.

Copy the provided VBA code.

Paste the code into the module you created.

Sub SortIPAddresses()
    Dim sourceRange As Range
    Dim destinationCell As Range
    Dim ipArray() As String
    Dim i As Long
    
    ' Prompt for selecting a range containing IP addresses
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the range containing IP addresses:", Type:=8)
    On Error GoTo 0
    
    ' Prompt for selecting a destination cell
    On Error Resume Next
    Set destinationCell = Application.InputBox("Select the destination cell for the sorted IP addresses:", Type:=8)
    On Error GoTo 0
    
    If Not (sourceRange Is Nothing) And Not (destinationCell Is Nothing) Then
        ' Resize the array to match the size of the selected range
        ReDim ipArray(1 To sourceRange.Rows.Count)
        
        ' Populate the array with IP addresses
        For i = 1 To sourceRange.Rows.Count
            ipArray(i) = sourceRange.Cells(i, 1).Text
        Next i
        
        ' Sort the array using a custom function
        Call CustomSort(ipArray)
        
        ' Output the sorted array to the destination cell
        destinationCell.Resize(UBound(ipArray)).Value = Application.Transpose(ipArray)
    Else
        MsgBox "Operation canceled. No valid range or destination cell selected."
    End If
End Sub

Function CustomSort(ByRef arr() As String)
    Dim i As Long, j As Long
    Dim temp As String
    Dim segArr1() As String, segArr2() As String
    
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            segArr1 = Split(arr(i), ".")
            segArr2 = Split(arr(j), ".")
            
            If CInt(segArr1(0)) > CInt(segArr2(0)) Or (CInt(segArr1(0)) = CInt(segArr2(0)) And CInt(segArr1(1)) > CInt(segArr2(1))) Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Function

Close the VBA editor by clicking the “X” button or pressing Alt + Q.

Press Alt + F8 to open the “Macro” dialog box.

Select SortIPAddresses from the list.

Click Run.

Input the range containing IP addresses when prompted.

Input the destination cell where you want the sorted IP addresses.

The VBA code will sort the IP addresses and output the result to the selected destination cell.

4. Video: Sort IP Address

This video tutorial, we’ll unravel the intricacies of sorting IP addresses in Excel, exploring three distinct methods – a formula-based approach using the TEXT function, the ‘Text to Columns’ feature, and a VBA code.

5. Related Functions

• Excel Text function
  The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
• Excel LEFT function
  The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
• Excel FIND function
  The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
• Excel RIGHT function
  The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….