Sort IP Addresses in Excel (With Example)

---

Often you may want to sort IP addresses in Excel.

Unfortunately, the default Sort button in Excel isn’t capable of sorting IP addresses in the correct order so we must use a custom formula instead.

The following example shows how to sort IP addresses in Excel in practice.

Example: How to Sort IP Addresses in Excel

Suppose we have the following column of IP addresses in Excel:

Now suppose we highlight the cell range A1:A8 and then click the Sort button in the Sort & Filter group on the Data tab along the top ribbon and attempt to sort the IP addresses from smallest to largest:

The IP addresses are not sorted correctly from smallest to largest.

For example, the IP address 122.45.65.90 should be the first but it is not.

Instead, we must create a helper column that pads each group of numbers in the IP address with zeros by typing the following formula into cell B2:

=NUMBERVALUE(TEXT(TEXTBEFORE(A2,".",1),"000")&TEXT(TEXTBEFORE(TEXTAFTER(A2,".",1),".",1),"000")&TEXT(TEXTBEFORE(TEXTAFTER(A2,".",2),".",1),"000")&TEXT(TEXTAFTER(A2, ".",3), "000"))

We can then click and drag this formula down to each remaining cell in column B:

Now we can highlight the cell range A1:B8, then click the Sort button in the Sort & Filter group on the Data tab along the top ribbon:

In the new window that appears, choose Helper from the Sort by dropdown menu:

Once you click OK, the IP addresses will be sorted from smallest to largest in the correct order:

We can see that the IP address 122.45.65.90 is now first.

Note: Feel free to delete the Helper column once you’ve sorted the rows now that you no longer need that column.