How to Sort IP Address in Excel (2 Effective Ways)

Proper sorting of IP addresses in Excel can help spot address ranges, detect  anomalies, configuration issues and maintain clean, searchable datasets. IP addresses (192.168.1.1 or 10.0.0.254 sequences)—might look like simple text strings, but sorting them correctly in Excel can be surprisingly difficult. Excel treats IP addresses as plain texts. So, sorting them the normal way results in incorrect alphabetical sorting.

This is the best method to sort IP addresses in Excel without any hassle. This custom formula converts each IP address into a single large number that preserves the correct numerical order. Since an IP address consists of four octets (e.g.192.168.1.5), the formula treats each octet as a number and uses base-256 math to calculate its unique value.

Suppose, this is our dataset containing some users and their IP addresses that we need to sort according to the IP addresses.

Follow the steps below to sort the IP addresses using a custom formula:

➤ Create a new column C adjacent to column B where we will apply the formula. We are naming this column as Sort.
➤ Select cell C2 and copy-paste this formula in the cell C2:

➤ The cell C2 will now show the four parts of the IP address as octet numbers.

➤ Click and drag the fill handle beside cell C2 until cell C11 to apply the formula in all the cells of column C.

➤ Select the entire table. Go to the Data tab and select Sort.

➤ Click the drop-down bar of Sort by option and select Sort to arrange the entire table according to Column C in ascending values. Click OK.

➤ Now, the entire dataset will be properly sorted according to the IP addresses. You can simply delete the helper column C if you don’t want to keep it afterwards.

If you regularly work with large IP address dataset, using a VBA (Visual Basic for Applications) macro is a fast and automated way to sort IP addresses correctly. The macro works by converting each IP address into a sortable number by incorporating a specific code.

To use the VBA Macro method, follow these steps below:

➤ Open your Excel Workbook and press  Alt  +  F11  to open the Visual Basic Editor window.

➤ Right click on the worksheet containing your data then go to Insert > Module.

➤ In the new module, copy and paste the following code.

Function IPToNumber(IP As String) As Double
    Dim parts() As String
    parts = Split(Trim(IP), ".")
    If UBound(parts) = 3 Then
        IPToNumber = CLng(parts(0)) * 16777216# + _
                     CLng(parts(1)) * 65536# + _
                     CLng(parts(2)) * 256# + _
                     CLng(parts(3))
    Else
        IPToNumber = 0
    End If
End Function

➤ The code will look like this in the module:

➤ From the File, select Close and Return to Microsoft Excel option.

➤ Create a helper column named Sort and in cell C2, type this formula:

This will now give a numerical output for cell B2.

➤ Click and drag the Fill handle beside cell C2 until cell C11 to apply the formula in all the cells of column C.

➤ Select the entire table. Go to the Data tab and select Sort.

➤ Click the drop-down bar of Sort by option and select Sort to arrange the entire table according to Column C from smallest to largest numbers. Click OK.

➤  Now, the entire dataset will be properly sorted according to the IP addresses. You can simply delete the helper column C if you don’t want to keep it afterwards.

Frequently Asked Questions

How to Format IP Addresses in Excel?

For a properly formatted IPv4 address, you need to make sure there are three dots in an IP address. These three dots will separate four different numeric octets whose values will be between 0 to 255. No extra spaces before, between or after the numbers should be present.

Will I Lose My Original IP Formatting if I Convert Them into Numbers?

To make sure that the original IP address formatting is untouched, we create a helper column in each method during sorting. This doesn’t affect the original addresses, it simply creates a sortable key for Excel to sort accurately.

Is There a Built-in Excel Function to Handle IP Addresses?

No, Excel does not have any built-in function to handle IP addresses. It only treats the IP addresses as normal texts. That’s why formulas, helper columns and VBA are used to parse, validate, and sort IPs.

Can I Sort IPv6 Addresses in Excel?

IPv6 addresses are much longer and hexadecimal. Sorting them correctly in Excel requires more complex parsing. It is not covered by standard IP sorting methods. Specialized software or scripts are recommended for handling IPv6 addresses.

Wrapping Up

It may not be as easy as clicking “Sort A to Z,” but with the correct method, sorting IP addresses in Excel is completely possible. Regardless of your preference for formula, assistance columns or a macro, each approach allows you to manage the order of IPs, ensuring that your data remains precise and well-structured.

The best tool for automation is a VBA macro if you’re working with large lists. Select the approach that best suits your workflow, and say goodbye to disorganized IP addresses.