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 address in Excel. The following picture illustrates how most of the methods work.

**Table of Contents**hide

**Download Practice Workbook**

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

**6 Ways to Sort IP Address in Excel**

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 address 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**:

**)**

`=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 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 address 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.

`=(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)))))))`

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 Sort and Filter Data in Excel (A Complete Guideline)**

**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 a 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.

**Related Content:** **How to Sort Data by Two Columns in Excel (5 Easy Ways)**

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

`=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))`

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.

**Related Content:** **How to Sort Drop Down List in Excel (5 Easy Methods)**

**Similar Readings:**

**Random Sort in Excel (Formulas + VBA)****How to Sort by Color in Excel (4 Criteria)****Sort Two Columns in Excel to Match (Both Exact and Partial Match)****How to Add Sort Button in Excel (7 Methods)****Sort by Ascending Order in Excel (3 Easy Methods)**

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

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

**Related Content:** **How to Undo Sort in Excel (3 Methods)**

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

`=SortIP(B5)`

**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:** **How to Use Sort Function in Excel VBA (8 Suitable Examples)**

**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 address are filled with zeros. Now you can sort them easily as before.

**Related Content:** **How to Sort ListBox with VBA in Excel (A Complete Guide)**

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

**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 in there too.

**Related Articles**

**How to Sort Multiple Columns in Excel (5 Quick Approaches)****Sort Columns in Excel without Mixing Data (3 Ways)****How to Arrange Numbers in Ascending Order in Excel Using Formula****Sum Using OFFSET and MATCH in Excel (With Alternative Options)****How to Sort Multiple Columns in Excel Independently of Each Other**

Thanks for the great article!

A small supplement: Why not sort with help of a function?

You may have thought that not everyone has Excel 365 and the SORT function available. But since February 2022 there is a solution for this: the functions SORT, SORTBY, FILTER, XLOOKUP, XMATCH, UNIQUE, SEQUENCE and RANDARRAY as UDFs for older versions of Excel (runs from Excel 2007).

Free download:

https://hermann-baum.de/excel/hbSort/en/

Greetings

Hermann

Thank you very much for your feedback, Hermann.

Great suggestion! Will keep in mind.