Jan 15, 2013 | 30 comments
**Download this tutorial (zipped PDF)**
A question came up on my Twitter feed this week – how to sort a list of IP Addresses in Excel. On the face of it, this should be simple. However, because Excel treats the IP addresses as text strings, not numbers, the sort order will not be correct, for example 172.24.161.200 will come before 172.24.161.8 (2 comes before 8 in an alphabetic sort). This tutorial explains how to sort a list of IP addresses correctly.
Scenario
For demonstration purposes I have a small list of IP addresses. In reality you’d likely have a much longer list. This list represents the IP addresses being used in a company and the location of the computer/device using them.
Sort the List – 1
Select the range A1:B6 and select Sort from the Data tab on The Ribbon (or the Data menu depending on which version of Excel you are using)
Sort the List – 2
Ensure that you tick the “My data has headers” box (1)
To sort this block of data by IP Address, select “IP Address” from the dropdown list in the “Column” section (2)
Notice that in the “Order” section (3) it shows as “A to Z”, indicating that Excel is treating the IP addresses as text strings rather than numeric values (If it treated them as numeric values it would say “smallest to largest”).
Click OK (4)
Sort the List – 3
The list of IP addresses is indeed sorted, but not numerically
Split the IP Address – 1
To correctly sort a list of IP addresses requires 3 steps. The first step is to split each IP address into separate cells. The screenshot below is the desired end result
Split the IP Address – 2
Rather than doing this manually, use Excel’s Text To Columns feature.
First of all, insert 4 blank columns to the right of column A, otherwise you will overwrite existing data when the Text to Columns command is issued.
Split the IP Address – 3
Select all the cells containing the IP addresses (A2:A6)
Select Text to Columns from the Data tab on the Ribbon (or the Data menu depending on which version of Excel you are using)
Split the IP Address – 4
The Text to Columns Wizard is launched. Ensure that “Delimited” is selected and click Next
Split the IP Address – 5
On step 2 of the Wizard, tick the “Other” box and type a full stop (period) into the box next to it. This indicates to Excel that the full stop character is the delimiter, i.e the character that is used to split up the text string into 4 separate parts.)
Click Next
Split the IP Address – 6
On step 3 of the Wizard, ensure that theDestinationfield contains =$A$2. This is the location where Excel will store the split text. Note that it is not necessary to select A2:D6. Excel will use as many columns as necessary (which is why extra columns were inserted earlier).
Click Finish
Split the IP Address – 7
This is the result
Sort the List – 1
The second step of the 3-step process is to sort the list.
Select A2:F6 and use the settings as per the screenshot. Notice that in the “Order” section it shows as “Smallest to Largest”, indicating that Excel is treating the IP address elements as numeric values.
Sort the List – 2
This is the result of the multi-column sort:
Recreating the Original IP Addresses
The final step of the 3-step process is to combine the elements back into a single string.
Type this formula into E2:
=CONCATENATE(A2,”.”,B2,”.”,C2,”.”,D2)
The Concatenate function is used to create a single string from multiple cells and/or literal characters. In this case the functionjoins together the contents of A2, a full stop, the contents of B2, a full stop, the contents of C2, a full stop and the contents of D2.
Copy the formula to E3:E6
Tidying Up – 1
Columns B:D can now be deleted, however, the formula in column E refers to these columns. If these columns are deleted, the formulas will break.Therefore, before deleting columns B:D:
Select cells E2:E6
Select the Copy command
Select A2
Click the lower section of the Paste button
Select Paste Values
The reason to use Paste Values rather than Paste, is so that Excel copies the result of the the formula rather than the formula itself.
Tidying Up – 2
Finally, delete columns B:D
**Download this tutorial (zipped PDF)**
**Updated 10th April 2011: I recently discovered an add-in that can sort a list of IP addresses with a couple of clicks. More information here
Sumeshon November 27, 2013 at 7:10 pm
You are awesome , it was little difficult as I am not that versed with EXCEL. But this worked with a little glitches for me.
You rock.Mike Thomason November 27, 2013 at 10:31 pm
Thank you! I’m glad you found the tutorial useful
Andre Robergeon December 3, 2013 at 2:31 pm
There’s a good Excel spreadsheet tool that has a function to convert IP addresses to decimal numbers. Once that’s done sorting is very easy.
andrewon February 16, 2014 at 2:56 am
You could just have the text to column output the results to Column D:E
open sort (expend selection) and follow above
no need to for formula and all the copy/paste, then you can simpley delete the D:E
andrewon February 16, 2014 at 2:56 am
whoops correct that D:G you only need to selec where the first one will go and Excel does the rest
Mike Thomason February 16, 2014 at 3:22 pm
Thanks or your comment Andrew. Not 100% sure what you mean though as column F had some content in it – the location of each PC. Also you need the CONCATENATE formula to put it all back together again so each IP address is stored in a single cell rather than split across 4 cells.
Chrison April 28, 2014 at 9:20 pm
Not sure how pasting the =CONCATENATE(A2,”.”,B2,”.”,C2,”.”,D2) into other rows besides the 2nd row will give you the IP for that corresponding row. I need a formula that I can paste into every cell in that column without editing that formula for that specific row. If I paste that “=CONCATENATE(A2,”.”,B2,”.”,C2,”.”,D2)” into every other row, it’s just going to give me the same output as the 2nd row since the formula is only reverencing A2, B2, C2, and D2. Is there a wildcard variable you can use instead of the 2 (for the 2nd row)?
Thanks,
Chris
- See AlsoDivida una celda en dos o más columnas en Excel Google SheetsNúmero dividido de la hoja de cálculo de Google en la celdaDescombinar todas las celdas en la hoja de cálculo de ExcelFórmula de división de texto en columnas de la hoja de cálculo de Google
Mike Thomason May 3, 2014 at 1:35 pm
Hi Chris. The CONCATENATE function in E2 is joining together the contents of the 4 cells to the left. When the function is copied down to rows 3,4,5 and 6, Excel does not copy the function itself but it copies the instruction “join together the values in the 4 cells to the left”, so the function in E3 will be =CONCATENATE(A3,”.”,B3,”.”,C3,”.”,D3) and so on
-Mike-
Calvinon July 22, 2014 at 5:45 am
Excellent thanks, the tutorial worked like a charm.
Aaronon December 17, 2014 at 7:29 pm
Hi Chris,
I know this is a day late and a dollar short, but just right and copy E2 then drag the cursor to the bottom of the column. Then select Ctrl-D on the keyboard and the rows will automatically update with the appropriate cell numbers.Nick Morganon February 26, 2015 at 12:48 pm
An alternative method could be to use ‘find a replace’ on a column of IP addresses to remove the decimals, e.g. find all 10.0.5. and replace with 1005. A sort can then be carried out on the column as it is in standard numerical format without the dots(e.g. 10051, 10052 etc.) Once the column has been sorted another find and replace can be carried out to revert back to IP format, e.g. find all 1005 and replace with 10.0.5.
Tomon March 4, 2015 at 7:55 am
Thanks for at good and precise tutoriel !
akshayon March 10, 2015 at 5:14 am
Hi mike, that was really a nice trick to sort IP addresses. Spliting the data and applying sorting and again combining. Thanks
LeeMon April 3, 2015 at 9:32 pm
Since mine were all in the same subnet 255.255.255.0 I added a column for LEN(ipaddress) sorted by that and then the IP.
Kimon April 20, 2015 at 9:58 am
For all Excel users dealing with IP address, there is one good Excel Add-In that detects IP address to country, region, city, latitude, longitude, ZIP code, ISP, domain name, time zone, connection speed, IDD code, area code, weather station code, weather station name, MCC, MNC, mobile brand name, elevation, and usage type.
Matton June 5, 2015 at 6:33 pm
Another easy option is to just use CTRL F to pull up the Find menu, click on the Replace tab, then replace all periods (.) with hyphens (-). Then select all the data and sort in ascending order. After they are in order you can use VLOOKUP on them. After you are done with everything just replace all hyphens (-) with periods (.) again and they will be in order and in the right format.
JimiDDon June 24, 2015 at 12:59 pm
An excellent way to deal with difficult ip addresses.
Much thanks.Ledskofon August 8, 2015 at 4:07 am
This doesn’t work for the same reason that having dots doesn’t work.
Zadon September 18, 2015 at 8:24 am
Well Done! This seems to be the easiest and simplest way possible…
Dvveon October 22, 2015 at 5:51 pm
If you add the zeros to the IP Adresses, they will sort properly
ex: 192.168.1.1 retyped as 192.168.11.001 as long as they are in the same subnetkarthickon November 6, 2015 at 7:25 am
thanks for a great idea.
BBNon March 25, 2016 at 4:23 pm
U JUST MADE MY DAY. THANKS A LOT MAN…..
Kamal Prasadon May 12, 2016 at 9:39 pm
Hi,
I need your help in making an excell formula for making IP database.
I have a device IP address (say 192.168.0.49), got to make another IP add (next hop: device IP Address+1 i.e 192.168.0.50).
another example… Device IP: 192.168.0.3, its next hop is 192.168.0.4
got to make for the list of more than 100 device IP address, so i believe excell would be easy way.
ThanksMike Thomason May 12, 2016 at 9:54 pm
If it’s as simple as 192.168.0.49, 192.168.0.50, 192.168.0.51…
Then type 192.168.0.49 into a cell, point your mouse at the bottom right hand corner of the cell and drag down. But I’m guessing it’s more complicated in that you can’t go past 255 for the 4th octet. Let me know
gaboon July 21, 2016 at 12:12 am
best reponse
Ray O.on July 27, 2016 at 10:39 pm
Rather than using “=concatenate(A2,B2,C2,D2)”, I find it easier to just type “=A2&B2&C2&D2”. Save some keystrokes…
Gerard Royon September 22, 2016 at 8:48 pm
Concatenate doesn’t always work maybe based on the cell formatting? A better way to join the cells back into one string may be like this: &”.”&
=D1&”.”&E1&”.”&F1&”.”&G1Linixon January 19, 2017 at 9:40 pm
Keep the original column of data. Create 4 new columns to the right of the IP address (B, C, D, E) . Copy the Ip addresses (Column A) and paste into Column B. Now proceed with the text to table using Column B.
Yothinsumphun, Yuttanaon March 4, 2017 at 12:24 pm
I used DEC2BIN function.
DEC2BIN(number, [places])
At first, use FIND function to identify “.” and separate 4 octets.
1st Octet : LEFT(A1,FIND(“.”,A1)-1)
2nd Octet : RIGHT(LEFT(A1,FIND(“.”,A1,FIND(“.”,A1)+1)-1),FIND(“.”,A1))
3rd Octet : 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)
4th Octet : RIGHT(A1,4-FIND(“.”,RIGHT(A1,4)))+0
then combine DEC2BIN function in each octets and places in 8 bit.
Finally, the formula is
=DEC2BIN(LEFT(A1,FIND(“.”,A1)-1),8)&”.”&DEC2BIN(RIGHT(LEFT(A1,FIND(“.”,A1,FIND(“.”,A1)+1)-1),FIND(“.”,A1)),8)&”.”&DEC2BIN(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),8)&”.”&DEC2BIN((RIGHT(A1,4-FIND(“.”,RIGHT(A1,4)))+0),8)
sorting with bit column A->Z.
SEAK, Teng-Fongon January 6, 2018 at 9:51 am
@Yothinsumphun, Yuttana
Your formulae to find 2nd and 4th octets are unfortunately wrong. You could use 111.22.3.4 to see what I mean.For 2nd octet, it should have been
RIGHT(LEFT(A1,FIND(“.”,A1,FIND(“.”,A1)+1)-1), LEN(LEFT(A1,FIND(“.”,A1,FIND(“.”,A1)+1)-1))-FIND(“.”,A1))
But a shorter one would be
LEFT(RIGHT(A1,LEN(A1)-FIND(“.”,A1)), FIND(“.”,RIGHT(A1,LEN(A1)-FIND(“.”,A1)))-1)The 4th octet should have been
RIGHT(A1,LEN(A1)-FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1))However, instead of using LEFT(), RIGHT() and embedded FIND(), I would have used the SUBSTITUTE() trick to find the positions of the dots.
On the other hand, I would have displayed zero-leading decimal values instead of binary valued-strings. Eg, the first octet would have been
TEXT(LEFT(A1,FIND(“.”,A1)-1), “000”)In this way, we can still sort the column and the displayed strings are easy to read and easy to spot errors.
