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.