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

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

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:
`=(VALUE(LEFT(B5,FIND(".",B5)-1))*10^9)+(VALUE(LEFT(RIGHT(B5,LEN(B5)-FIND(".",B5)),FIND(".",RIGHT(B5,LEN(B5)-FIND(".",B5)))-1))*10^6)+VALUE(LEFT(RIGHT(RIGHT(B5,LEN(B5)-FIND(".",B5)),LEN(RIGHT(B5,LEN(B5)-FIND(".",B5)))-FIND(".",RIGHT(B5,LEN(B5)-FIND(".",B5)))),FIND(".",RIGHT(RIGHT(B5,LEN(B5)-FIND(".",B5)),LEN(RIGHT(B5,LEN(B5)-FIND(".",B5)))-FIND(".",RIGHT(B5,LEN(B5)-FIND(".",B5)))))-1))*10^3+VALUE(RIGHT(RIGHT(RIGHT(B5,LEN(B5)-FIND(".",B5)),LEN(RIGHT(B5,LEN(B5)-FIND(".",B5)))-FIND(".",RIGHT(B5,LEN(B5)-FIND(".",B5)))),LEN(RIGHT(RIGHT(B5,LEN(B5)-FIND(".",B5)),LEN(RIGHT(B5,LEN(B5)-FIND(".",B5)))-FIND(".",RIGHT(B5,LEN(B5)-FIND(".",B5)))))-FIND(".",RIGHT(RIGHT(B5,LEN(B5)-FIND(".",B5)),LEN(RIGHT(B5,LEN(B5)-FIND(".",B5)))-FIND(".",RIGHT(B5,LEN(B5)-FIND(".",B5)))))))`

It converts the IPs to decimal numbers.

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

Step 2:

`=IF(0,"#####FIRSTOCTET#####","")&TEXT(LEFT([@IP],FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),1))-1),"000")&"."&IF(0,"#####SECONDOCTET#####","")&TEXT(MID([@IP],FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),1))+1,FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),2))-FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),1))),"000")&"."&IF(0,"#####THIRDOCTET#####","")&TEXT(MID([@IP],FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),2))+1,FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),3))-FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),2))),"000")&"."&IF(0,"#####FOURTHOCTET#####","")&TEXT(MID([@IP],FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),3))+1,IF(ISERROR(FIND("/",[@IP])),LEN([@IP]),FIND("/",[@IP])-1)-FIND(CHAR(134),SUBSTITUTE([@IP],".",CHAR(134),3))),"000")&IF(0,"#####CIDR#####","")&IF(ISERROR(FIND("/",[@IP])),"",RIGHT([@IP],LEN([@IP])-FIND("/",[@IP])+1))`

It will fill all IPs with zeros.

Step 3:

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

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.

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:

`=SortIP(B5)`

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.

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

## Things to Remember

• Method 4 only works if 3 of the 4 octets of the IP address have the same digits.

## Related Articles

<< Go Back to Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio