Generate a specific range of IP address in MS.Excel

excel, excel-formula, vba

Solution

Your starting IP address is 10.1.1.1. That is 0A010101 IN HEX. Put that in a cell, let's say A2. Put this formula in B2,

=HEX2DEC(LEFT(A2, 2))&"."&HEX2DEC(MID(A2, 3, 2))&"."&HEX2DEC(MID(A2, 5, 2))&"."&HEX2DEC(RIGHT(A2, 2))

Now put this formula in A3,

=DEC2HEX(HEX2DEC(A2)+1, 8)

Copy the formula from B2 to B3 then fill A3:B3 down as far as you want.

Problem

I want to generate a specific range of IP from `192.168.1.0` to `192.168.255.255` How can I do that job in Microsoft Excel? I used these formulas, but not work for all range of IP address: ``` Excel IP Addresses Increment the Third Octet ="10.1."&ROWS($A$1:A1)&".1" Excel IP Addresses Increment the Fourth Octet ="10.1.1."&ROWS($A$1:A1) ```

Original source