Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do create a formula to add IP addresses?
Try
Function NextIPAddress(IP As String) As String Dim Arr As Variant Dim V As Integer Arr = Split(IP, ".") V = CInt(Arr(UBound(Arr))) V = V + 1 If V Mod 4 = 0 Then Arr(UBound(Arr)) = V Else V = V + 4 - (V Mod 4) Arr(UBound(Arr)) = CStr(V) End If NextIPAddress = Join(Arr, ".") End Function -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "jeffreyj" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create reference formula that looks through all sheets for matchin | Excel Worksheet Functions | |||
How do I create a 26 'tier' IF formula? | Excel Discussion (Misc queries) | |||
How can I create a formula for Salutation | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
In two workbooks, how do I create a formula that will look at a o. | Excel Discussion (Misc queries) |