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 ;
