Excel TCP/IP spreadsheet formula help

[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?

 

[smartin]

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?

If you don't mind using about 30 helper columns here is a kludgy
solution for you:

http://vfdrake.home.comcast.net/files/excel/ip_address_calculator.xls

This makes heavy use of Harlan Grove's binary math formulae (which can
be viewed here:
http://groups.google.com/group/micr....functions/browse_frm/thread/129693728f1559b6
)

 

[Satti Charvak]

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

 

[Clubsprint]

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

 

[Clubsprint]

A bit messy but this worked,thanks

If you don't mind using about 30 helper columns here is a kludgy solution
for you:

http://vfdrake.home.comcast.net/files/excel/ip_address_calculator.xls

This makes heavy use of Harlan Grove's binary math formulae (which can be
viewed here:
http://groups.google.com/group/micr....functions/browse_frm/thread/129693728f1559b6 )

 

[smartin]

A bit messy but this worked,thanks

Indeed. A much cleaner approach would be to use VBA functions to parse
the addresses and do the binary math.