How do I get cells in Excel that contain IP addresses to sort properly?

Question

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:

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

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?

Answer 1

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 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:

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

Answer 2

The most easiest, 3 steps Solution I can suggest you are,,,

1. Select the IP Address Column, apply Text to Column command.

2. In adjacent Column write this Formula

   =CONCATENATE(B3,".",C3,".",D3,".",E3)

3. Finally Sort in Ascending order.

Check the Screen Shot.

NB:

Red is Original IP Address (in Column A).

Green after applied Text to Column (Column B to E).

Black is After applied Concatenate & Sorting (Column F).

The reason is very simple originally IP address is Text Data and Excel doesn't accepts any Cell Format to turn it to Number.

Hope this help you.

Answer 3

Here'a a VBA function I wrote some time ago to solve the same problem. It generates a padded version of an IPv4 address which sorts correctly.

Function SortAddress(Address As String)                     '   format address as XXX.XXX.XXX.XXX to permit sorting

Dim FirstByte As Integer, LastByte As Integer, I As Integer

SortAddress = ""
FirstByte = 1

For I = 0 To 2                                          '   process the first three bytes

    LastByte = InStr(FirstByte, Address, ".")           '   find the dot
                                                        '   append the byte as 3 digits followed by dot
    SortAddress = SortAddress & Format(Mid(Address, FirstByte, LastByte - FirstByte), "000\.")

    FirstByte = LastByte + 1                            '   shift the start pointer

Next I

SortAddress = SortAddress & Format(Mid(Address, FirstByte), "000") ' process the last byte

End Function

Simple example:

Result

Formulas

You can sort by the 'Sortable' column and hide it.

Answer 4

Here is an answer that will take only 1 column of your table and converts the IPv4 address to base 10 numbering.

Since you are putting your data in column "M", this starts in cell M2 (M1 being the label). Encapsulating it as code gives one terrible mess, so I have used blockquote:

=INT(LEFT(M2, FIND(".", M2) - 1)) * 256 ^ 3 + INT(MID(M2, FIND(".", M2) + 1, FIND(".", M2, FIND(".", M2) + 1) - FIND(".", M2)-1)) * 256 ^ 2 + INT(MID(M2, FIND(".", M2, FIND(".", M2) + 1) + 1, FIND(".", M2, FIND(".", M2, FIND(".", M2) + 1) + 1) - FIND(".", M2, FIND(".", M2) + 1) - 1)) * 256 + INT(RIGHT(M2, LEN(M2) - FIND(".", M2, FIND(".", M2, FIND(".", M2) + 1) + 1)))

Not exactly the most easily readable formula, but you can just copy and paste into your cell (preferably N2 or something else in the same row as your first IP address). It presumes proper formatting of the IP address as error correction in the formula would make it even worse for human parsing.

Answer 5

If you don't want to use formulas or VBA, use Power Query. (In Excel 2016, Get & Transform, in Excel 2010 or 2013 install PowerQuery add-in to follow along).

1. Bring the table into the PowerQuery editor.
2. Duplicate the column by right clicking "Duplicate Column"
3. "Split Column" by delimiter, on the Home tab. Select "Each occurrence of the delimiter"
4. Sort each column Asc. from left to right.
5. Select the previously split columns, right click and remove, close and load.

Answer 6

The is a similiar one liner that transforms the octets into 3 digit fields that allows proper sorting.

10.1.0.15 becomes 10001000015.

=LEFT(B85, FIND(".", B85) - 1) * 1000000000
+ MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 1)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - FIND(".", B85) - 1) * 1000000
+ MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 3)) - FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - 1) * 1000
+ RIGHT(B85, LEN(B85) - FIND("x", SUBSTITUTE(B85, ".", "x", 3)))

Answer 7

As shown in question, column M are the IP addresses (IPv4), starting from M2.

By getting the good points from everyone's answer, here is my solution. Only 1 helper column is needed. We try to format the IPv4 addresses into 012.198.043.009 format and then sort them:

• 12.198.43.9 to 12 198 43 9, then to 012.198.043.009

---

1. Format IPv4 addresses into 012.198.043.009 format by inputting in N2, and fill downwards:

   = TEXT( LEFT(SUBSTITUTE(M2, ".", "      "), 3    ), "000") & "."
   & TEXT(  MID(SUBSTITUTE(M2, ".", "      "), 8, 5 ), "000") & "."
   & TEXT(  MID(SUBSTITUTE(M2, ".", "      "), 15, 7), "000") & "."
   & TEXT(RIGHT(SUBSTITUTE(M2, ".", "      "), 3    ), "000")
   

2. Sort by column N

---

Explaination

By SUBSTITUTEing the dot . with 6 spaces, we get the following, so that they can be extracted correctly:

                   |123456789|123546789|123456789|
1.1.1.1         ->  1      1      1      1
11.11.11.11     ->  11      11      11      11
111.111.111.111 ->  111      111      111      111
                    =1=    ==2==  ===3===

• Character 1-3 contains and only contains the first part.
• Character 8-12 contains and only contains the second part.
• Character 15-21 contains and only contains the third part.
• Rightmost 3 characters contains and only contains the fourth part.

And then, extract and format each part by TEXT(..., "000").

Answer 8

If you are using a recent version of Excel in Windows you can use the following formula to calculate the 32 bit decimal value of the IP address.

=SUMPRODUCT(FILTERXML("<I><o v="""&SUBSTITUTE([@ipbase],".","""/><o v=""")&"""/></I>","//o/@v"),{16777216;65536;256;1})

You can then sort using the derived decimal value. Substitute [@ipbase] with the cell location of a valid IP4 address.

The "FILTERXML" function only works in Windows, so you're out of luck if you're using another operating system.

Answer 9

tl;dr

Convert IPv4 dotted decimal in cell A2 to decimal integer:

=SUM(MAP(TEXTSPLIT(A2,"."),SEQUENCE(,4),LAMBDA(o,i,o*256^(4-i))))

Convert IPv4 dotted decimals in column with range A2:A11 to decimal integers:

=MAP(CHOOSECOLS(A2:A11,1),LAMBDA(ip,SUM(MAP(TEXTSPLIT(ip,"."),SEQUENCE(,4),LAMBDA(o,i,o*256^(4-i))))))

---

Padding the IP addresses with 0s is an incorrect approach that produces the right output in this case (see: wrong, and luck), and sorting over each octet independently is unnecessarily complicated.

The dotted decimal notation already corresponds to the natural numbers with the same intuitive order you are wanting. We just need a way to convert natural numbers between this notation and the familiar base-10 decimal notation Excel understands.

IPv4 addresses in dotted decimal notation have the form A.B.C.D, but, the periods . are only used to enhance readability, so let's write it as ABCD. Each of the 4 letters represents a single digit with 256 (0-255) possible values — i.e., a 4-digit number in base-256.

Thus, our goal is to convert notation of natural numbers from base-256 to base-10.

Recall the formula for converting any number n (base-N) to t (base-10) is simply the sum of each digit multiplied by its place value (digits in n are separated with a period .):

n	Base (N)	Formula	t
4.2.0	10	4*10² + 2*10¹ + 0*10⁰	420
1.1.0.1	2	1*2³ + 1*2² + 0*2¹ + 1*2⁰	13
127.0.0.1	256	127*256³ + 0*256² + 0*256¹ + 1*256⁰	2130706433

This formula can easily be expressed in Excel, assuming your IPv4 dotted decimal address is in cell A2:

=SUM(MAP(TEXTSPLIT(A2,"."),SEQUENCE(,4),LAMBDA(o,i,o*256^(4-i))))

Instead of auto-generating the formula using the click-and-drag technique, you can also apply this formula across a cell range with something like the following (using A2:A11 as a column of dotted decimal IPv4 addresses):

=MAP(CHOOSECOLS(A2:A11,1),LAMBDA(ip,SUM(MAP(TEXTSPLIT(ip,"."),SEQUENCE(,4),LAMBDA(o,i,o*256^(4-i))))))