Excel TCP/IP spreadsheet formula help

  • Thread starter Thread starter Clubsprint
  • Start date Start date
C

Clubsprint

We ban external networks fom access to our net and I'm trying to set up
aspreadsheet to help me identify single addresses when I get queries about
if someone is blocked. I've set up a spreqadsheet of the banned networks and
I want to autofill the stat and end address of the sub/super nets. So that
it loooks somewhat like this

Name ,Address , Mask , Start
,End
bantimewarner,207.250.0.0,255.255.0.0,207.250.0.1,207.250.255.254

bannigeria216,216.133.174.0,255.255.255.0,216.133.174.1,216.133.174.254





What I need is the formula to get the start and end addresses from the IP
and mask and what format do I set the cells to?
 
Hi Clubsprint,

The formula's are as follows:


for start address:

=LEFT(B3,LEN(B3)-1)&1

I just replaced the last digit with "1"

for end address:

=IF(LEN(C3)-LEN(SUBSTITUTE(C3,255,"S"))=4,LEFT(B3,FIND(".",B3,FIND(".",B3)+1))&"255.254",LEFT(B3,FIND(".",B3,FIND(".",B3,FIND(".",B3)+1)+1))&"254")


please note the following for the formulas to work properly:

Name is in column A
Address is in Column B
Mask is in column C


--
Please click Yes if you like the post.

Kind Regards,
Satti Charvak
Only an Excel Enthusiast
 
Hi Satti
Close, worked for the Cass Cs but fell over whn calculating the As and Bs.
See below result.
Name, Network, Netmask, Start Address, End Address
banmediacom12.214.45.0 12.214.45.0 255.255.255.0 12.214.45.1 12.214.45.254
banasianet121.0.0.0 121.0.0.0 255.0.0.0 121.0.0.1 121.0.0.254
banasianet123.0.0.0 123.0.0.0 255.128.0.0 123.0.0.1 123.0.0.254
 
Back
Top