» Sort IP addresses in Excel (2023)

January 15, 2013|30 comments

**Download this tutorial (compressed PDF)**

There was a question in my Twitter feed this week: how to sort a list of IP addresses in Excel. On the surface, it should be simple. However, since Excel treats IP addresses as text strings, not numbers, for example, the sort order will not be correct. early172.24.161.8(2 comes before 8 in alphabetical order). This tutorial explains how to properly sort a list of IP addresses.


» Sort IP addresses in Excel (1)

For demonstration purposes I have a small list of IP addresses. In fact, you probably have a much longer list. This list shows the IP addresses used by the company and the location of the computer/device using them.

Sort list - 1

» Sort IP addresses in Excel (2)

Select the range A1:B6 and selectPuzzle outofDanishtab on the ribbon (orDanishdepending on the version of Excel you are using)

Sort List - 2

» Sort IP addresses in Excel (3)

Be sure to check"My data has headers"box (1)

To sort this data block by IP address, select"IP Address"from the drop-down list"Column"domain 2)

Note that in"Series"Section (3) shows how"To do Z", indicating that Excel treats IP addresses as text strings, not numeric values ​​(if it treated them as numeric values, it would say "from lowest to highest").


Sort List - 3

» Sort IP addresses in Excel (4)

The list of IP addresses is sorted, but not numerically

Split the IP address - 1

» Sort IP addresses in Excel (5)

There are 3 steps to get the IP address list in the correct order. The first step is to divide each IP into separate cells. The screenshot below shows the final desired result

IP Address Split - 2

» Sort IP addresses in Excel (6)

Instead of doing it manually, use Exceltext in columnsmode.

First, insert 4 empty columns to the right of column A. Otherwise, when you send Text to columns, you will overwrite the existing data.

IP Address Separation - 3

» Sort IP addresses in Excel (7)

Select all cells containing IP addresses (A2:A6)

ChooseText in columnsofDanishon cassette (orDanishdepending on the version of Excel you are using)

Split the IP address - 4

» Sort IP addresses in Excel (8)

The Convert Text to Columns wizard will start. make sure of that"Confined"is selected and clickNext

Split the IP address - 5

» Sort IP addresses in Excel (9)

In step 2 of the wizard, select"But"field and enter apoint(dot) in the box next to it. This means to Excel that the dot character is a delimiter, i.e. a character used to split a text string into 4 separate parts).


Split the IP address - 6

» Sort IP addresses in Excel (10)

In step 3 of the wizard, make sure theDESTINYthe field contains=$A$2. This is the location where Excel will save the delimited text. Note that it is not necessary to select A2:D6. Excel will use as many columns as it needs (that's why the extra columns were inserted earlier).


Split the IP address - 7

» Sort IP addresses in Excel (11)

This is the result

Sort list - 1

» Sort IP addresses in Excel (12)

The second step in the 3-step process is to sort the list.

ChooseA2:F6and use the settings according to the screenshot. Note that the Order section displays "from lowest to highest", indicating that Excel treats the IP address elements as numeric values.

Restore original IP addresses

» Sort IP addresses in Excel (14)

The final step of the 3-step process is to concatenate the elements back into a string.

Enter this formulaE2:


The Concatenate function is used to create a single string from multiple cells and/or literal characters. In this case, the function combines content A2, point, content B2, point, content C2, point and content D2.

Copy the formula toQ3: Q6

Cleaning - 1

» Sort IP addresses in Excel (15)

Columns B:D can now be deleted, however the formula in column E is applied to these columns. If these columns are removed, the formulas will break. Therefore, before removing columns B:D:

select cellsQ2: Q6
Click on the bottom of the filePastabutton
Choosepaste the values

The reason you use paste values ​​instead of pasting is that Excel copies the result of the formula, not the formula itself.

Cleaning - 2

» Sort IP addresses in Excel (16)

Finally, remove columns B:D

**Download this tutorial (compressed PDF)**

**Updated April 10, 2011: I recently discovered a plugin that can sort a list of IP addresses with just a few clicks. More informationHere

  1. » Sort IP addresses in Excel (17)

    sumaNovember 27, 2013 at 7:10 pm

    You are amazing, it was a bit difficult because I am not that good at excel. But it worked with some minor glitches for me.
    You command

  2. » Sort IP addresses in Excel (18)

    Mike ThomasOctober 27, 2013 at 10:31 pm

    Thanks! I'm glad you found the tutorial useful.

  3. » Sort IP addresses in Excel (19)

    Andres RobertDecember 3, 2013 at 2:31 pm

    It's goodExcel spreadsheet toolwhich has the function of converting IP addresses to decimal numbers. Once this is done, sorting is very easy.

  4. » Sort IP addresses in Excel (20)

    AndreiFebruary 16, 2014 at 2:56 am

    You can simply display the text in the result column in column D:E

    open classification (problem selection) and monitoring

    no type and all copy/paste required, you can just delete D:E

  5. » Sort IP addresses in Excel (21)

    AndreiFebruary 16, 2014 at 2:56 am

    Oh, fix D:G, just choose where it goes first and Excel does the rest

  6. » Sort IP addresses in Excel (22)

    Mike ThomasFebruary 16, 2014 at 3:22 pm

    Thanks for your comment Andrew. I'm not 100% sure what he meant though, because column F had some content: the location of each computer. You also need the CONCATENATE formula to combine everything so that each IP address is stored in a single cell, not split across 4 cells.

  7. » Sort IP addresses in Excel (23)

    ChrisApril 28, 2014 at 9:20 pm

    I'm not sure how pasting =CONCATENATE(A2,"".,B2,"".,C2,"".,D2) into rows other than the second row will give you the IP address for the corresponding row. I need a formula that I can paste into every cell in this column without editing that formula for that particular row. If I paste this "=CONCATENATE(A2,"".,B2,"".,C2,"".,D2)" into alternate rows, I get the same result as the second row, as the formula only honors A2, B2 , C2 and D2 Is there a wild card that can be used instead of 2 (for the second row)?



  8. » Sort IP addresses in Excel (24)

    Mike ThomasMay 3, 2014 at 1:35 pm

    Hi Chris. The CONCATENATE function in E2 concatenates the contents of the left 4 cells. When the function is copied to rows 3,4,5 and 6, Excel does not copy the function itself, but copies the statement "combine the values ​​in the left 4 cells", so the function in E3 will be =CONCATENATE(A3, ".,B3, .,C3,"".,D3) and so on


  9. » Sort IP addresses in Excel (25)

    calvinJuly 22, 2014 at 5:45 am

    Excellent thanks, the tutorial worked like a charm.

  10. » Sort IP addresses in Excel (26)

    AaronDecember 17, 2014 at 7:29 pm

    Hi Chris,
    I know you're a day late and a dollar short, but the correct thing to do is copy E2 and then drag it to the bottom of the column. Then select Ctrl-D on your keyboard and the rows will automatically update with the appropriate cell numbers.

  11. » Sort IP addresses in Excel (27)

    Nick MorganFebruary 26, 2015 at 12:48 pm

    An alternative method could be to use a "replace find" in the IP address column to remove the decimal places, e.g. find all 10.0.5. and replace the column with 1005. You can then sort the column in standard number format without periods (eg 10051, 10052, etc.) After sorting the column, you can do another search and replace to return to IP format, e.g. 1005 and replace with 10.0.5.

  12. » Sort IP addresses in Excel (28)

    ThomasMarch 4, 2015 at 7:55 am

    Thanks for a good and accurate tutorial!

  13. » Sort IP addresses in Excel (29)

    AksayeMarch 10, 2015 at 5:14 am

    Hi Mike, that was a very nice trick for sorting IP addresses. Split data and apply sorting and reintegration. Thanks

  14. » Sort IP addresses in Excel (30)

    LeeMApril 3, 2015 at 9:32 pm

    Since all of mine were on the same subnet, I added a column for the LEN (IP address) sorted by the one followed by the IP address.

  15. » Sort IP addresses in Excel (31)

    KimApril 20, 2015 at 9:58 am

    For all you Excel users dealing with IP addresses, there is a good thingExcel add-inwhich locates IP address country, region, city, latitude and longitude, zip code, ISP, domain name, time zone, connection speed, IDD code, area code, weather station code, weather station name, MCC, MNC, mobile brand telephony, height and type of use.

  16. » Sort IP addresses in Excel (32)

    ForeignJune 5, 2015 at 6:33 pm

    Another easy option is to just use CTRL F to bring up the Find menu, click the Replace tab, and then replace all periods (.) with dashes (-). Then select all data and sort them in ascending order. Once you've sorted them, you can use VLOOKUP on them. When you're done with everything, just replace all dashes (-) with periods (.) and they'll be organized and in the right format.

  17. » Sort IP addresses in Excel (33)

    JimiDDJune 24, 2015 at 12:59 pm

    A great way to deal with difficult IP addresses.
    Thank you very much.

  18. » Sort IP addresses in Excel (34)

    LedskofAugust 8, 2015 at 4:07 am

    This doesn't work for the same reason that having points doesn't work.

  19. » Sort IP addresses in Excel (35)

    fenceSeptember 18, 2015 at 8:24 am

    well done! This seems the easiest and simplest way...

  20. » Sort IP addresses in Excel (36)

    FromOctober 22, 2015 at 5:51 pm

    If you add zeros to the IP addresses, they will be sorted correctly
    eg: is rewritten as if they are on the same subnet

  21. » Sort IP addresses in Excel (37)

    shrapnelNovember 6, 2015 at 7:25 am

    Thanks for the great idea.

  22. » Sort IP addresses in Excel (38)

    BBNMarch 25, 2016 or 4:23 pm


  23. » Sort IP addresses in Excel (39)

    Kamal PrasadMay 12, 2016 at 9:39 pm

    I need your help to create a perfect formula to create an IP database.
    I have a device IP address (for example, I need to add another IP address (next hop: device IP address + 1, ie
    another example… Device IP:, next hop is
    I need to make a list of 100+ device IP addresses so I thought Excel would be the easiest way.

  24. » Sort IP addresses in Excel (40)

    Mike ThomasMay 12, 2016 at 9:54 pm

    If it's as simple as,,…

    Then type in the cell, place your mouse in the lower right corner of the cell and drag it down. But I guess it's more complicated because you can't pass 255 for the fourth octet. Inform me

  25. » Sort IP addresses in Excel (41)

    old manJuly 21, 2016 at 12:12 pm

    the best answer

  26. » Sort IP addresses in Excel (42)

    lightning or.July 27, 2016 at 10:39 pm

    Instead of using "=concatenate(A2,B2,C2,D2)", I find it easier to write "=A2&B2&C2&D2". Save some keys...

  27. » Sort IP addresses in Excel (43)

    Gerarda RoyaSeptember 22, 2016 at 8:48 pm

    Concatenation doesn't always work, maybe based on cell format? A better way to concatenate cells back into a string might be: &"."&

  28. » Sort IP addresses in Excel (44)

    LinixJanuary 19, 2017 at 9:40 pm

    Keep the original data column. Create 4 new columns to the right of the IP address (B, C, D, E). Copy the IP addresses (column A) and paste them into column B. Now go to the table text using column B.

  29. » Sort IP addresses in Excel (45)

    Yothinsummphun, YuttanaMarch 4, 2017 at 12:24 pm

    I used DEC2BIN function.

    DEC2BIN(number? [parts])

    First, use the FIND function to determine the "." and split 4 octets.

    1st octet: LEFT(A1,SEARCH(“.”,A1)-1)

    Second octet: RIGHT(LEFT(A1,SEARCH("..,",A1,SEARCH("..",A1)+1)-1),SEARCH("..",A1))

    Octet 3: RIGHT(LEFT(A1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)-1),FIND(“.”,A1 FIND(“”,A1,FIND(“.”,A1)+1)+1)-FIND(“.”,A1,FIND(“.”,A1)+1)-1)

    Octet 4: RIGHT(A1,4-SEARCH(“.”,RIGHT(A1,4)))+0

    then attach the DEC2BIN function to each octet and set it to 8 bits.

    Finally there is the formula.

    =DEC2BIN(LEFT(A1,SEARCH(“.”,A1)-1),8)&.””&DEC2BIN(RIGHT(LEFT(A1,SEARCH(“.”,A1,SEARCH(“.”,A1)+ 1)-1),SEARCH(“”,A1)),8)&.””&DEC2BIN(RIGHT(LEFT(A1,SEARCH(“.“,A1,SEARCH(“.“,A1,SEARCH(“. ” ,A1)+1)+1)-1),SEARCH(“.”,A1,SEARCH(“.”,A1,SEARCH(“.”,A1)+1)+1)-SEARCH(“” ,A1 ,SEARCH(“.”,A1)+1)-1),8)&.””&DEC2BIN((RIGHT(A1,4-SEARCH(“.”,RIGHT(A1,4)))+0) 8)

    sort by a column of bits A->Z.

  30. » Sort IP addresses in Excel (46)

    SEAK, Teng-FongJanuary 6, 2018 at 9:51 am

    @Yothinsummphun, Yuttana
    Unfortunately, your formulas for finding the second and fourth octets are incorrect. You can use to see what I mean.

    For the second octave, it should be
    RIGHT(LEFT(A1,SEARCH(“.",A1,SEARCH(“.”,A1)+1)-1), LONG(LEFT(A1,SEARCH(“.”,A1,SEARCH(“; A1 )+1)-1))-ΑΝΑΖΗΤΗΣΗ(“;A1))
    but it would be shorter
    LEFT(RIGHT(A1, LENGTH(A1) - FIND("..", A1)), FIND(".", RIGHT(A1, DEBT(A1) - FIND("..", A1))) -1 )

    The fourth octave should be

    However, instead of using the built-in LEFT(), RIGHT(), and FIND() , you would use the SUBSTITUTE() trick to find the points' positions.

    On the other hand, it will display decimal values ​​starting from zero instead of strings with binary values. For example, this would be the top eight.
    TEXT(LEFT(A1, FIND("..", A1)-1), "000")

    This way we can still sort the column and the strings displayed are easy to read and easy to debug.

Post a comment


Top Articles
Latest Posts
Article information

Author: Kerri Lueilwitz

Last Updated: 05/30/2023

Views: 6132

Rating: 4.7 / 5 (67 voted)

Reviews: 90% of readers found this page helpful

Author information

Name: Kerri Lueilwitz

Birthday: 1992-10-31

Address: Suite 878 3699 Chantelle Roads, Colebury, NC 68599

Phone: +6111989609516

Job: Chief Farming Manager

Hobby: Mycology, Stone skipping, Dowsing, Whittling, Taxidermy, Sand art, Roller skating

Introduction: My name is Kerri Lueilwitz, I am a courageous, gentle, quaint, thankful, outstanding, brave, vast person who loves writing and wants to share my knowledge and understanding with you.