Sorting IP Addresses & Drag down IP Addresses formula in Excel

Sometimes you may need to assign IP address to your colleagues, and the range of IP address may generate from 192.168.1.1 to 192.168.10.1 for instance. What can you do to generate them easily? Actually, the normal Auto Fill feature cannot work with this, or you can just create them manually by entering them in cells one by one? In this article we will show you how to write formula to make Auto Fill (drag down formula) feature work for IP address.
Also IP address sorting is not possible with normal sort feature, In this article we will show you how to write formula to make sort feature work for IP address.
How to use formula more efficiently and save time when using Excel?
If you want to generate the range of IP Address from 192.168.1.1 to 192.168.10.1 (the increment number locates in the third octet), (any incrimental range) the following formula will help you.

1. Select a blank cell (says cell A1), enter first IP 192.168.1.1 then select another blank cell beside it  (say cell B1) enter below formula into the formula bar, and then press the Enter key. See screenshot: 
=VALUE(LEFT(A1,FIND(".",A1)-1))*2^24+VALUE(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1))*2^16+VALUE(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-FIND(".",A1,FIND(".",A1)+1)-1))*2^8+VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)))






2. Then you can see the first IP Address is converted in IP Decimal.
3. Select a blank cell (says cell A2), enter last IP 192.168.10.1 of the range then drag down formula from cell B1 to B2.
4. Then will get IP Decimal value of last IP Address in range. 
5. Subtract first IP Decimal values from last IP Decimal to know the count of IPs in the Range. Enter Formula=B2-B1 into the formula bar, and then press the Enter key. See screenshot:
6. Now select a blank cell (says cell D1), enter first IP decimal 3232235777 then select another blank cell beside it  (say cell E1) enter below formula into the formula bar, and then press the Enter key. See screenshot: 
=ROUNDDOWN(D1/2^24,0)&"."&ROUNDDOWN(MOD(D1,2^24)/2^16,0)&"."&ROUNDDOWN(MOD(MOD(D1,2^24),2^16)/2^8,0)&"."&MOD(MOD(MOD(D1,2^24),2^16),2^8)






7. Next step is to drag down formula along with IP decimal. Simple way to do same for large range is select cell D2 & type 1 as cell value then type D2305 [IP decimal difference + 1] in name box which will navigate to cell D2305; with D2305 cell selected press Ctrl + Shift + Up Arrow key which will select cell range from D2 to D2305. Press Ctrl + D key to fill entire range with Cell value of D2 (that is value "1") See Screenshot:
8. Next step is select cell D2 & type next incremental IP Decimal as cell value 3232235778 & Select cell D1, D2 and double click on small square of selection box which will fill all incremental IP decimal till cell D2305. See Screenshot:
9. Similarly select Cell E1 & double click on small square of selection box which will fill all incremental IP Address till cell E2305. See Screenshot:

How to Sort IP address in Excel

You can use above technique of converting IP Address to IP decimal & sort IP decimal which will sort IP Address inturn.

    

Comments

Unknown said…
Cat6 Cable Manufacturers Supplier

We have gained a huge popularity in the market by offering excellent quality assortment of Cat6 Cable Manufacturers Supplier. These are manufactured from high grade raw materials and latest technology by our expert team. Best quality and low prices we are supplier. Call us +91 98990 00668.

mandeepcables.com/cat-6-product-770606.html
Unknown said…
Thanks for sharing this information with us, your website is really awesome.instagram bio Instagram usernames instagram captions

TIC Academy said…
Wonderful blog for SAP which I have seen and it's absolutely great stuff on SAP Topics. Thanks for such a cool blog about SAP topics. Very good explanation on SAP concepts we do SAP Training in Chennai.
Regards,
SAP Training Institutes in Chennai | Best SAP Training in Chennai
Mithun said…
Excellent post with lots of information about Networking Technology.
CCNA Training in Chennai | Hardware and Networking Training in Chennai
itsbest10 said…
Top 5 Best Switch Brand In India
1. Legrand
2. GM Modular
3. Anchor
4. Havells
5. Simon
John Hardy said…
This one is very good for students who study in IP address. Thanks for sharing this article. Keep it up. Now it's time to avail 1 litre water bottle for more information.

Popular posts from this blog

How To Add Print Button to Blogger Posts

INSTALL CISCO VPN CLIENT ON WINDOWS 10 (32 & 64 BIT). FIX REASON 442