Possibly the easiest, fastest way to sort IP addresses in Excel 2013

My method to quickly and EASILY sort IP addresses in Excel 2013

Want a quick, easy way to sort 254 IP addresses?

You can do this just using half a dozen clicks and entering two ip addresses!

No need for splitting the address into 4 columns - or using 'strings' and concatenation.

This is EASY

Hi,

If like me, you have created an Excel spreadsheet detailing computer equipment and IP Addresses, you will have discovered that  the IP addresses are not sorted correctly.

The issue arises because the IP addresses in the spreadsheet are treated as text, and therefore are not sorted correctly. Whilst you can search online for various ways to sort the IP addreses, they often are complicated (including splitting the address into four columns and using concatenation)  when all someone wants is just a quick fix.

Development

Using Excel 2013, I started out finding a better solution by re-typing the last digits of the address in a new SORTED column and sorting them by this new column, then realised that I only need to type the end of the first IP address, because when you get to the second one it automatically gives you the whole lot!

Summary of solution

  • Into your existing spreadsheet of IP addresses, insert a new column using Excel 2013
  • Type the last couple of digits of the first ip address
  • Type the start of the second IP address
  • Observe that the column now gives you the result you need.
  • Press the Enter key

That's it! Done.  

You can sort or edit as usual

So, my method for a quick sort - a little rough and ready - but fast and easy. 

In order to make the instructions here very basic, to deal with most users, I have split them into 5 steps - so excuse the long winded explanations.

Give it a try.  You will discover that if you do not exactly follow the instructions here you might get strange results...  but you'll soon get the feel of it, discover any limitations and be able to adjust it to taste.

'Ol Codger

April 2015

Here is the problem and solution step by step..

'The Problem'

When you sort typical IP address in Excel, they are 'not completely' sorted correctly.

Example of the issue:

I show a fictitious but typical example below.  You can see I have put a red circle around the addresses that do not really seem to be in the right order

The fast, easy solution

Step 1.   

In this typical list in your Excel 2013 spreadsheet, insert a new column to the left of the IP addresses.

Step 2

Just to make things clear, I have labelled this column 'sorted'. You don't need to do this though.

Go carefully with this one. If it doesn't work, go back to step 1.

Firstly, you must now enter just the final digits of the first device's IP address into the new Sorted column.

Take a look in the example below, you can see that I have entered 10 in the sorted column. That's because the device uses 192.168.1.10 . 

Step 3

STOP, GO CAREFULLY.... 

Secondly we will start to add the second IP address on your list - but go carefully with this one - it's different..

(You can see in my example, the second device is 192.168.1.100.  The last digits of the second device IP address is 100...)

So, just start to press down the '1' for this second IP address of 100 and, 'voila!',  you will see all the IP numbers automatically appear underneath - grayed out as shown underneath in the picture.

This is the main element of my instructions. It produces a 'sorted' list with no effort.

You are nearly done!  But if you don't get this happening and it doesn't work, go back to step 1.

Step 4.

Now that you can see these numbers appearing in grey in the Sorted column, simply press the Enter key so they turn to black.

Step 5

The final step.

That's my revelation completed.

All you need to do now is simply do a normal Excel sort ! - but using the 'sorted' column as our list index.

Note:    If you don't know how to do this, just click once on the 'Sorted' column header,

Select DATA from the tab headings at the top of Excel, click on the Sort icon in the toolbar.

Choose to sort by the 'Sorted' column... usually that's Column A.   

You are done!

RESULT!!

Step 6 (optional)

Yes, you are all finished.

If you wish, you can delete the temporary 'sorted' column.

I hope you found this helpful!

'Ol Codger

End

Sorting IP addresses;  Sort IP address in Excel; IP address in wrong order ; IP address sort ; addresses in wrong order ;

Unfortunately, this only works if the first 3 octets are the same subnet.  

For example, I could not sort a spreadsheet using this method if I had addresses listed under 172.16.3.x, 172.16.4.x, and 172.16.19.x.

If I only had the last octet to worry about, then your solution is perfect!  Thanks for sharing.  

And unfortunately if you have 6000 entries it is also useless. Using the same thinking (With ordering another column to sort the one of interest) you can use this formula:

=(VALUE(LEFT(A1;FIND(".";A1)-1))*10^9)+(VALUE(LEFT(RIGHT(A1;LEN(A1)-FIND(".";A1));FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1)))-1))*10^6)+VALUE(LEFT(RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1))));FIND(".";RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1)))))-1))*10^3+VALUE(RIGHT(RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1))));LEN(RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1)))))-FIND(".";RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1)))))))

Assume the IP address is in A1, paste this to B1. 

Hi,

You can easily sort IP-addresses by spliting them to their 4 values.

(I random generated IP-addresses with randbetween(1,255))

IP Device details
121.111.45.173 A
68.11.178.181 B
174.14.154.73 C
61.100.34.74 D
181.90.7.97 E
152.182.111.128 F
32.137.26.143 G
101.157.41.248 H
44.242.188.72 I
251.14.80.134 J
6.110.71.49 K
218.175.107.74 L
119.179.222.173 M
117.32.67.170 N
117.196.45.220 O
215.76.138.55 P
202.59.83.2 Q
71.154.84.216 R
78.217.11.26 S
98.18.209.251 T
103.119.159.107 U
253.65.126.95 V
234.201.232.255 W
75.84.43.141 X
204.134.118.156 Y

Copy the IP's to a free column and TextToColumns, "." as delimiter.

Custom sort:

Done:

IP Device details IP
6.110.71.49 K 6 110 71 49
32.137.26.143 G 32 137 26 143
44.242.188.72 I 44 242 188 72
61.100.34.74 D 61 100 34 74
68.11.178.181 B 68 11 178 181
71.154.84.216 R 71 154 84 216
75.84.43.141 X 75 84 43 141
78.217.11.26 S 78 217 11 26
98.18.209.251 T 98 18 209 251
101.157.41.248 H 101 157 41 248
103.119.159.107 U 103 119 159 107
117.32.67.170 N 117 32 67 170
117.196.45.220 O 117 196 45 220
119.179.222.173 M 119 179 222 173
121.111.45.173 A 121 111 45 173
152.182.111.128 F 152 182 111 128
174.14.154.73 C 174 14 154 73
181.90.7.97 E 181 90 7 97
202.59.83.2 Q 202 59 83 2
204.134.118.156 Y 204 134 118 156
215.76.138.55 P 215 76 138 55
218.175.107.74 L 218 175 107 74
234.201.232.255 W 234 201 232 255
251.14.80.134 J 251 14 80 134
253.65.126.95 V 253 65 126 95

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Custom sort:

Which version of Excel are you using?

This sort window is not available in 2013




























































































































































Hi Rasoul,

I am using Excel 2013. You'll findd in Sort & Filter, Custom Sort. Select the first column to sort on and add levels.

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

And unfortunately if you have 6000 entries it is also useless. Using the same thinking (With ordering another column to sort the one of interest) you can use this formula:

=(VALUE(LEFT(A1;FIND(".";A1)-1))*10^9)+(VALUE(LEFT(RIGHT(A1;LEN(A1)-FIND(".";A1));FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1)))-1))*10^6)+VALUE(LEFT(RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1))));FIND(".";RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1)))))-1))*10^3+VALUE(RIGHT(RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1))));LEN(RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1)))))-FIND(".";RIGHT(RIGHT(A1;LEN(A1)-FIND(".";A1));LEN(RIGHT(A1;LEN(A1)-FIND(".";A1)))-FIND(".";RIGHT(A1;LEN(A1)-FIND(".";A1)))))))

Assume the IP address is in A1, paste this to B1. 

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)

Assume the IP address is in A1, paste this to B1.

sorting with bit column A->Z or Z->A.

Simply use www.advanced-ip-scanner.com that already sorts IPs correctly.
After 15 years microsoft has yet to include a simple menu function for IP address sorting. Seriously? Come on MS!
Patrick Burwell, VP, iQor, Inc.
 
 

Discussion Info


Last updated September 23, 2020 Views 21,304 Applies to: