IP Address Range Calculator

B

bleauhaus

New Member
Joined
Oct 3, 2014
Messages
2
Im sure this has been posted but after reading over this forum and others i still cannot find a solution.

Given the first two columns of IP addresses i want to generate the third or even fourth Column


[TABLE="width: 327"]
<tbody>[TR]
[TD]StartIP[/TD]
[TD]EndIP[/TD]
[TD]Total IPs[/TD]
[TD]Total /24s[/TD]
[/TR]
[TR]
[TD]1.32.0.0[/TD]
[TD]1.32.127.255[/TD]
[TD="align: right"]32,766[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]2.15.0.0[/TD]
[TD]2.15.255.255[/TD]
[TD="align: right"]65,534[/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD]31.206.0.0[/TD]
[TD]31.206.255.255[/TD]
[TD="align: right"]65,534[/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD]39.112.0.0[/TD]
[TD]39.127.255.255[/TD]
[TD="align: right"]1,048,574[/TD]
[TD="align: right"]4096[/TD]
[/TR]
[TR]
[TD]42.16.0.0[/TD]
[TD]42.47.255.255[/TD]
[TD="align: right"]2,097,150[/TD]
[TD="align: right"]8190[/TD]
[/TR]
[TR]
[TD]42.186.0.0[/TD]
[TD]42.186.255.255[/TD]
[TD="align: right"]65,534[/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD]42.195.0.0[/TD]
[TD]42.195.255.255[/TD]
[TD="align: right"]65,534[/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD]49.51.0.0[/TD]
[TD]49.51.255.255[/TD]
[TD="align: right"]65,534[/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD]59.110.0.0[/TD]
[TD]59.111.255.255[/TD]
[TD="align: right"]131,070[/TD]
[TD="align: right"]512[/TD]
[/TR]
[TR]
[TD]83.210.0.0[/TD]
[TD]83.210.31.255[/TD]
[TD="align: right"]8,190[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]85.208.0.0[/TD]
[TD]85.209.255.255[/TD]
[TD="align: right"]131,070[/TD]
[TD="align: right"]512[/TD]
[/TR]
[TR]
[TD]100.0.0.0[/TD]
[TD]100.41.255.255[/TD]
[TD="align: right"]2,752,512[/TD]
[TD="align: right"]10752[/TD]
[/TR]
[TR]
[TD]101.202.0.0[/TD]
[TD]101.202.255.255[/TD]
[TD="align: right"]65,534[/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD]104.141.0.0[/TD]
[TD]104.141.255.255[/TD]
[TD="align: right"]65,534[/TD]
[TD="align: right"]256[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Forgot to finish: So changing the last octet of the first address from 0 to 255 should reduce the number of IP addresses spanned by 255.
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Here is another attempt. Source https://answers.yahoo.com/question/index?qid=20120127062343AAdS2Qm

Excel Workbook
ABCD
1StartIPEndIPTotal IPsTotal /24s
21.32.0.01.32.127.25532,768128
32.15.0.2552.15.255.255 65,281256
431.206.0.031.206.255.255 65,536256
539.112.0.039.127.255.255 1,048,5764096
642.16.0.042.47.255.255 2,097,1528190
742.186.0.042.186.255.255 65,536256
842.195.0.042.195.255.255 65,536256
949.51.0.049.51.255.255 65,536256
1059.110.0.059.111.255.255 131,072512
1183.210.0.083.210.31.255 8,19232
1285.208.0.085.209.255.255 131,072512
13100.0.0.0100.41.255.255 2,752,51210752
14101.202.0.0101.202.255.255 65,536256
15104.141.0.0104.141.255.255 65,536256
Sheet1
 
Upvote 0
Peter_SSs said:
Going on what Momentman did with his udf, here's a formula solution that produces the same results. Not the results you quoted, but you haven't told us how to get those numbers. Subtracting 2 would do it but I don't know the logic for why you would do that.
Click to expand...


Peter,

If i remember well, one should subtract two because, for example, in row 2

1.32.0.0 is the Network address and cannot be assigned to a host
and
1.32.255.255 is the Broadcast address and also cannot be assigned to a host.

In short, these two addresses cannot be assigned to hosts.

M.
 
Upvote 0
0 to 255 is the 256 that is required being zero is counted, multiples of eight which was the old bus width of many of the earlier computers, 8 bits to a byte
 
Upvote 0
Marcelo Branco said:
Peter,

If i remember well, one should subtract two because, for example, in row 2

1.32.0.0 is the Network address and cannot be assigned to a host
and
1.32.255.255 is the Broadcast address and also cannot be assigned to a host.

In short, these two addresses cannot be assigned to hosts.

M.
Click to expand...
Thanks Marcelo. I'm the same as Momentman & FormR when it comes to IP knowledge :)
 
Upvote 0
Peter_SSs said:
Sheet2

ABC
StartIPEndIP
1.32.0.01.32.127.255
2.15.0.02.15.255.255
31.206.0.031.206.255.255
39.112.0.039.127.255.255
42.16.0.042.47.255.255
42.186.0.042.186.255.255
42.195.0.042.195.255.255
49.51.0.049.51.255.255
59.110.0.059.111.255.255
83.210.0.083.210.31.255
85.208.0.085.209.255.255
100.0.0.0100.41.255.255
101.202.0.0101.202.255.255
104.141.0.0104.141.255.255

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]Total IPs[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]32,768[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]65,536[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]65,536[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]1,048,576[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]2,097,152[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"]65,536[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: right"]65,536[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: right"]65,536[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: right"]131,072[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: right"]8,192[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="align: right"]131,072[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: right"]2,752,512[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="align: right"]65,536[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="align: right"]65,536[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C2=(RIGHT(SUBSTITUTE(B2,"."," "),3)-RIGHT(SUBSTITUTE(A2,"."," "),3)+1)*(MID(SUBSTITUTE(B2,".",REPT(" ",100)),200,100)-MID(SUBSTITUTE(A2,".",REPT(" ",100)),200,100)+1)*(MID(SUBSTITUTE(B2,".",REPT(" ",100)),100,100)-MID(SUBSTITUTE(A2,".",REPT(" ",100)),100,100)+1)*(LEFT(B2,FIND(".",B2))-LEFT(A2,FIND(".",B2))+1)

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...
This formula will produce the same results you show in your table above...

=SUM(256^{3,2,1,0}*(MID(SUBSTITUTE(B2,".",REPT(" ",99)),{1,100,199,298},99)-MID(SUBSTITUTE(A2,".",REPT(" ",99)),{1,100,199,298},99)))+1
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top