Excel – How to get cells in Excel that contain IP addresses to sort properly

ip addressmicrosoft excelsorting

I am currently working with a large list of IP addresses (thousands of them).

However, when I sort the column containing the IP addresses, they don't sort in a way that is intuitive or easy to follow.

For example, if I enter IP Addresses as follows:

enter image description here

And then if I sort in ascending order I get this:

enter image description here

Is there a way for me to format the cells so that, for example, an IP address of 17.255.253.65 appears after 1.128.96.254 and before 103.236.162.56 when sorted in ascending order?

If not, is there another way for me to achieve this ultimate aim?

Best Answer

As you may have realised, your IP addresses are treated as text and not numbers. They are being sorted as text, which means that addresses beginning with "162" will come before addresses beginning with "20." (because the character "1" comes before the character "2".

You can use the formula provided in this answer: https://stackoverflow.com/a/31615838/4424957 to split the IP address into its parts.

If your IP addresses are in columns A, add columns B-E as shown below.

enter image description here

Enter the formula

=VALUE(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",999)),(B$1)*999-998,999)))

in cell B2 and copy it to columns B-E in all rows to get the four parts of each IP address. Now sort the whole range by columns B through E (in that order) as shown below:

enter image description here

If you don't want to see the helper columns (B-E), you can hide them.

Related Question