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

sort ip address in excel


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!

dataset to sort ip address in excel


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.

enter formula to sort ip address

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.

sort & filter tool to sort ip adress

Step 4: Expand the selection while sorting as follows.

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

ip address sorted

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.

ip address sorted using alternative formula

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.

select ataset to sort ip address

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

text to column wizard to sort ip address

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

mark data as delimited

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

text separator dot

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.

data selection to sort ip address using text to columns wizard

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

custom sort in excel

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.

sorting by columns in excel

You can hide or delete the octets if you want.

ip address sorted using text to column wizard

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.

excel table to sort ip address

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.

ip address filled with zeros

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

Finally, the IP addresses are sorted.

ip address sorted using excel table

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


Similar Readings:


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.

flash fill 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.

ip address sorted using flash fill

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.

openning vba in excel

Step 2: From the Insert tab, select Module.

new module in vba to sort ip address

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

code for UDF to sort ip address

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)

enter UDF to sort ip address

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.

ip address sorted using UDF in excel

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

vba code to sort ip address

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.

select cells

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

ip address filled with zeros to sort using vba

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

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

2 Comments
  1. 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

Leave a reply

ExcelDemy
Logo