No matter how technical you are, once in a while you have to work with Excel. And, if you are a network engineer, you will need to put IP addresses in Excel. However, Excel and IP addresses do not seem to go along quite well. Often times, Excel will simply break them and make your entire spreadsheet worthless. In this post, I show you one good way to manage IP addresses in Excel. No extensions, no add-ons, no VBA – just plain old Excel.
Excel IP Addresses
The source of the problem
Before we can tackle this challenge, we need to know our enemy (Excel in this case). Excel does not like IP addresses because they almost look like numbers. It looks like you are writing some numbers in the order of the billions, using dots as separators for the thousands. For example, take “10.100.240.112”. It is a valid IP address, but it looks like 10 billion 100 million 240 thousand 112. Excel sees that and wants to help you.
To help you, it wants to better process the number so that you can reference it later in formulas. The dots are an issue for that, so it simply removes them.
The easy way
Now that we know the problem, we can apply an easy solution that may be good for most cases. Since Excel do not like the dots, we simply never write them. Instead, we pad each octet with one or two zero. So, for example, the IP address 192.12.1.340 will be 192012001340. This way, it is a valid digit, but you know you have three numbers for the first octet, three for the second, three for the third, and three for the fourth.
Excel will be so happy with that that it will never – ever – complain. This solution can be good if you are writing an inventory of devices. In fact, you can see the IP address almost immediately, once you understand the mechanism. However, it is not so good when you have to write some more operative documents. In that case, you may want to have an IP address that you can copy in the prompt and ping, as is. Copy and paste eliminates the human error.
Of course, your command prompt (or any other technical software), will only understand the IP address with the dots, so we need to find a different solution.
The best way to go
The best way to go when it comes to Excel and IP addresses is to write them as they are. However, there are additional things you can do to ensure nothing breaks. Plus, we will see a trick to help you sort multiple IP addresses.
First, format the column where you want to put the IP addresses as text. This will make things easier, particularly if you do that before writing the IP addresses in it.
You may still get some warning messages on the cells from time to time. These don’t really cause any harm, but they are bothersome to see. Thus, I recommend ignoring them as soon as you encounter them. Once we are there, we can push ourselves a little bit further with sorting.
Sorting IP addresses in Excel
How to sort IP addresses in Excel without VBA? It is very easy, but we need to use some additional columns in our table, one for every octet. We don’t have to write every octet by hand, of course. Instead, we can simply use a few formulas to calculate them. If the IP address is in cell A2, you can use the following formulas. The first obtains the first octet.
=LEFT(A2; FIND("."; A2)-1)This obtains the second octet:
=MID(A2; FIND("."; A2)+1; FIND("."; A2; FIND("."; A2)+1)-FIND("."; A2)-1)This is for the third instead:
=MID(A2; FIND(".";A2;FIND("."; A2)+1)+1; FIND("."; A2; FIND(".";A2;FIND("."; A2)+1)+1)-FIND(".";A2;FIND("."; A2)+1)-1)And, finally, this is for the last octet.
=RIGHT(A2;LEN(A2)-FIND(".";A2;FIND(".";A2;FIND(".";A2)+1)+1))Once everything is in place, your Excel file will look something like the one below.
Now, if we want to sort using these new columns, we have to use sorting on multiple columns. With the cursor in the table, in the Editing menu inside the home panel, select Custom sort.
It may ask to expand your selection, if so just accept that. Then, using the “Add level button” add four filtering criteria and configure the as follows.
Now just click okay, and you will have your IP addresses sorted! Easy enough!
Conclusion
In conclusion, handling IP addresses in Excel is not that bad – you just need to know how to do so. With these techniques, I have been managing thousands of IP addresses on a daily basis for a big enterprise, and I have been able to achieve everything I needed quickly and easily.
Are you using Excel to handle your IP addresses? What do you think of these techniques, and what is yours? Let me know in the comments below.
