sortinggoogle-sheetsip-addressgoogle-sheets-formula

Sort spreadsheet by column containing IP addresses (one IP address per cell)

Using the default sorting engine, the following list is considered sorted:

10.0.0.219
10.0.0.22
10.0.0.223

This is incorrect. The sorting should be:

10.0.0.22
10.0.0.219
10.0.0.223

It is not safe to assume the first three octets will be static. For instance, other IP addresses include:

10.35.10.11
10.28.66.30
Solution

  • I realize this is an old post, but in the interest of providing a working solution, I present the following.

    Just put this formula in an adjacent cell and update the references to point to the cell containing your IP address (A1 in this example). This will produce a result similar to 010.121.008.030, which can then be sorted (correctly) alphabetically. Then set the column width to zero and voila. Time to enjoy a cup of coffee.

    =TEXT(MID(A1,1,FIND(".",A1)),"000")&"."&TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-1-FIND(".",A1)),"000")&"."&TEXT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-1-FIND(".",A1,FIND(".",A1)+1)),"000")&"."&TEXT(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)),"000")