Excel TCP/IP spreadsheet formula help
 

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?

Excel TCP/IP spreadsheet formula help
 

Clubsprint wrote:
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/file...calculator.xls

This makes heavy use of Harlan Grove's binary math formulae (which can
be viewed he
http://groups.google.com/group/micro...9693728f1559b6
)

Excel TCP/IP spreadsheet formula help
 

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,FIN D(".",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" wrote:

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?

Excel TCP/IP spreadsheet formula help
 

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


"Satti Charvak" wrote in message
...
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,FIN D(".",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" wrote:

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?

Excel TCP/IP spreadsheet formula help
 

A bit messy but this worked,thanks

"smartin" wrote in message
...
Clubsprint wrote:
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/file...calculator.xls

This makes heavy use of Harlan Grove's binary math formulae (which can be
viewed he
http://groups.google.com/group/micro...9693728f1559b6 )

Excel TCP/IP spreadsheet formula help
 

Clubsprint wrote:
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.