A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do create a formula to add IP addresses?



 
 
Thread Tools Display Modes
  #1  
Old June 2nd 06, 10:33 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

I am trying to make a formula that adds the last part of an IP address, such
that when I type in one IP address suchas 147.202.70.132, they automatically
fill in below sequentially with 133, 134, 135, etc. Can anyone help me with
this? Thanks.

Ads
  #2  
Old June 2nd 06, 10:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

I'd use a VBA function.

Function NextIPAddress(IP As String) As String
Dim Arr As Variant
Arr = Split(IP, ".")
Arr(UBound(Arr)) = Arr(UBound(Arr)) + 1
NextIPAddress = Join(Arr, ".")
End Function

You can then call this from a worksheet cell with code like
=NextIPAddress(A1)

You be using Excel 2000 or later in order to use the Split and
Join functions.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"jeffreyj" > wrote in message
...
>I am trying to make a formula that adds the last part of an IP
>address, such
> that when I type in one IP address suchas 147.202.70.132, they
> automatically
> fill in below sequentially with 133, 134, 135, etc. Can
> anyone help me with
> this? Thanks.
>



  #3  
Old June 2nd 06, 10:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

Just drag and copy them down, Excel does it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"jeffreyj" > wrote in message
...
> I am trying to make a formula that adds the last part of an IP address,

such
> that when I type in one IP address suchas 147.202.70.132, they

automatically
> fill in below sequentially with 133, 134, 135, etc. Can anyone help me

with
> this? Thanks.
>



  #4  
Old June 2nd 06, 10:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

I never knew that.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Bob Phillips" > wrote in message
...
> Just drag and copy them down, Excel does it.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing
> direct)
>
> "jeffreyj" > wrote in
> message
> ...
>> I am trying to make a formula that adds the last part of an IP
>> address,

> such
>> that when I type in one IP address suchas 147.202.70.132, they

> automatically
>> fill in below sequentially with 133, 134, 135, etc. Can
>> anyone help me

> with
>> this? Thanks.
>>

>
>



  #5  
Old June 2nd 06, 10:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

"jeffreyj" wrote:
> I am trying to make a formula that adds the last part of an
> IP address, such that when I type in one IP address suchas
> 147.202.70.132, they automatically fill in below sequentially
> with 133, 134, 135, etc.


If A1 contains the initial IP address, A2 can be:

=REPLACE(A1, FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,
LEN(A1),
MIN(255,
MID(A1, FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1, LEN(A1)) + 1))

When you copy the formula down the column, Excel will update
the relative cell references appropriately.

Obviously it would be better to put the FIND() result into a helper
cell to avoid evaluating it twice.
  #6  
Old June 2nd 06, 11:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

"Bob Phillips" wrote:
> Just drag and copy them down, Excel does it.


I shoulda remembered that myself. But on second thought, I
think Chip's suggestion of using a macro is the best idea because
it can be extended to cover the more general case of "incrementing
an IP address". The OP assumes that requires only incrementing
the last octet, which is the case for Class C IP addresses. But in
the case of a Class A, B and D IP addresses, incrementing 255
may carry over to the next octet. For example, the next address
after 1.2.3.255 is 1.2.4.0.
  #7  
Old June 5th 06, 05:59 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

I know Excel does this. I need a formula so that it updates automatically.
I want to set a cell equal to an IP address in a different worksheet A. And
when I update the IP address in worksheet A, it updates it in B and fills in
the IP addresses below, automatically

"Bob Phillips" wrote:

> Just drag and copy them down, Excel does it.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "jeffreyj" > wrote in message
> ...
> > I am trying to make a formula that adds the last part of an IP address,

> such
> > that when I type in one IP address suchas 147.202.70.132, they

> automatically
> > fill in below sequentially with 133, 134, 135, etc. Can anyone help me

> with
> > this? Thanks.
> >

>
>
>

  #8  
Old June 5th 06, 06:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

The VBA procedure I posted will do that.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"jeffreyj" > wrote in message
...
>I know Excel does this. I need a formula so that it updates
>automatically.
> I want to set a cell equal to an IP address in a different
> worksheet A. And
> when I update the IP address in worksheet A, it updates it in B
> and fills in
> the IP addresses below, automatically
>
> "Bob Phillips" wrote:
>
>> Just drag and copy them down, Excel does it.
>>
>> --
>>
>> HTH
>>
>> Bob Phillips
>>
>> (replace xxxx in the email address with gmail if mailing
>> direct)
>>
>> "jeffreyj" > wrote in
>> message
>> ...
>> > I am trying to make a formula that adds the last part of an
>> > IP address,

>> such
>> > that when I type in one IP address suchas 147.202.70.132,
>> > they

>> automatically
>> > fill in below sequentially with 133, 134, 135, etc. Can
>> > anyone help me

>> with
>> > this? Thanks.
>> >

>>
>>
>>



  #9  
Old June 5th 06, 07:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

Thanks Chip. I will use that.

"Chip Pearson" wrote:

> The VBA procedure I posted will do that.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
> "jeffreyj" > wrote in message
> ...
> >I know Excel does this. I need a formula so that it updates
> >automatically.
> > I want to set a cell equal to an IP address in a different
> > worksheet A. And
> > when I update the IP address in worksheet A, it updates it in B
> > and fills in
> > the IP addresses below, automatically
> >
> > "Bob Phillips" wrote:
> >
> >> Just drag and copy them down, Excel does it.
> >>
> >> --
> >>
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> (replace xxxx in the email address with gmail if mailing
> >> direct)
> >>
> >> "jeffreyj" > wrote in
> >> message
> >> ...
> >> > I am trying to make a formula that adds the last part of an
> >> > IP address,
> >> such
> >> > that when I type in one IP address suchas 147.202.70.132,
> >> > they
> >> automatically
> >> > fill in below sequentially with 133, 134, 135, etc. Can
> >> > anyone help me
> >> with
> >> > this? Thanks.
> >> >
> >>
> >>
> >>

>
>
>

  #10  
Old June 5th 06, 09:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default How do create a formula to add IP addresses?

Chip,

Say I wanted to add onto this function, and instead of just giving the next
IP address, I want to check to see if the next IP address is divisible by 4.
If it is divisible by 4 then put it in the cell, if not, put the next
available address that is divisible by 4. How would I do this? thanks.


"Chip Pearson" wrote:

> I'd use a VBA function.
>
> Function NextIPAddress(IP As String) As String
> Dim Arr As Variant
> Arr = Split(IP, ".")
> Arr(UBound(Arr)) = Arr(UBound(Arr)) + 1
> NextIPAddress = Join(Arr, ".")
> End Function
>
> You can then call this from a worksheet cell with code like
> =NextIPAddress(A1)
>
> You be using Excel 2000 or later in order to use the Split and
> Join functions.
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "jeffreyj" > wrote in message
> ...
> >I am trying to make a formula that adds the last part of an IP
> >address, such
> > that when I type in one IP address suchas 147.202.70.132, they
> > automatically
> > fill in below sequentially with 133, 134, 135, etc. Can
> > anyone help me with
> > this? Thanks.
> >

>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
create reference formula that looks through all sheets for matchin BMW Excel Worksheet Functions 2 November 4th 05 05:20 PM
How do I create a 26 'tier' IF formula? callum Excel Discussion (Misc queries) 5 October 23rd 05 04:48 PM
How can I create a formula for Salutation cs_vision Excel Discussion (Misc queries) 6 October 8th 05 12:31 AM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
In two workbooks, how do I create a formula that will look at a o. socaldave469 Excel Discussion (Misc queries) 1 April 22nd 05 10:01 PM


All times are GMT +1. The time now is 04:59 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.