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?

  • 3
    Sorting isn't affected by cell formatting. – Blackwood Dec 24 '17 at 0:18
  • 1
    That's interesting - I was pretty sure that a cell's format would affect sorting in some cases and thought maybe this was one of those. Thanks for the clarification! – Monomeeth Dec 24 '17 at 0:27
  • 1
    What in Australia are you planning to use this spreadsheet for? There are better tools for IP address management or for handling firewall/proxy group definitions. Are you reporting to management? – Criggie Dec 25 '17 at 10:06
  • 1
    And Bruce help you if any of this needs to handle IPv6 addresses! – Criggie Dec 25 '17 at 10:06
up vote 41 down vote accepted

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.

  • 2
    Thanks, this does the trick! :) I'll give it a little while just in case anyone has a more elegant solution, but if not I'll definitely mark this as the accepted answer. Thanks again! – Monomeeth Dec 24 '17 at 0:45
  • 12
    Instead of splitting to four columns, maybe it'd be better to "concatenate" the bytes like first*256^3+second*256^2+third*256+fourth in one column? – Ruslan Dec 24 '17 at 7:42
  • Especially since IPv4 addresses are actually just 32 bit numbers. The four 4 bit number way of displaying IP addresses is solely for human readability. – Kat Jan 2 at 16:44
  • @Kat I agree that would work, and it only requires one helper column instead of four. But the formula in that one column would be rather long, so I chose to use four columns (I would probably hide them in any case). – Blackwood Jan 2 at 16:50

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.

enter image description here

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.

  • 3
    This terrible. You never ever want to mix up data from different rows. – MaxW Dec 24 '17 at 23:58
  • @MaxW ,, I'm not getting exactly what you want to say !! – Rajesh S Dec 25 '17 at 6:10
  • 1
    He means you should have included the original column A in the sort, so the iP addresses would be the same on each row. – Bandersnatch Dec 26 '17 at 0:21
  • 1
    No,,, nothing wrong I did,, the OP demand was to Sort the IP Address in Natural order,, (Number) and the Column A in RED is un-sorted. Column F has Sorted IP Address in BLACK. – Rajesh S Dec 26 '17 at 9:11
  • 1
    +1 This is the easiest solution on the page. @RajeshS maybe change the word 'adjusted' to 'adjacent' in your answer - i thought you meant one of the Text To Column columns there. @MaxW, the layout is just for demonstration. If you want the original IP sorted just ignore step 2. That massive "VALUE(TRIM(MID(SUBSTITUTE" formula in the accepted answer should be replaced by 'Text To Columns' and the rest of the answer is essentially the same as this. – mcalex Jan 24 at 12:33

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

Result

Formulas

Formulas

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

  • 4
    I just want to note that the sortable IP address will parse as a different address from the original one (because some idiot chose to interpret those as octal). So only use them for sorting and don't treat them as proper IPs. – CodesInChaos Dec 24 '17 at 14:42
  • If you're going to pull vba out why not just have vba do the sorting – Richard Tingle Dec 24 '17 at 18:36
  • 1
    @RichardTingle 1. It's (a little) more work 2. I might want to sort the list in several different ways at different moments – grahamj42 Dec 24 '17 at 22:28

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.

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.

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)))
  • 2
    For the sort to work, you need to also pad the first octet with leading zeros. – Blackwood Dec 28 '17 at 0:53

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.

  1. Prepare formatted IP address 001.002.003.004 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:

                   |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, format each part by TEXT().

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.