| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formular to add to a IP address
I know the default gateway of over 2000 routers. They are in a master
spreadsheet which has created a vlookup from another sheet within the same workbook. If the default gateway is 100.100.100.1 in a second column I want to produce a ip address of 100.100.100.20. Once I have this I can copy the formular to make a third IP address of 100.100.100.25 I know that the last octet in the dafault gateway will always end in 1 and I know that the second IP address will always end in 20 How do I get the second address to calculate to to be always 20. The first 3 octets, although follow a formular across the 2000 sites will differ If anyone can help it would be most grateful I don;t fancy having to do it manually Sean... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formular to add to a IP address
"Sean" skrev i en meddelelse
... I know the default gateway of over 2000 routers. They are in a master spreadsheet which has created a vlookup from another sheet within the same workbook. If the default gateway is 100.100.100.1 in a second column I want to produce a ip address of 100.100.100.20. Once I have this I can copy the formular to make a third IP address of 100.100.100.25 I know that the last octet in the dafault gateway will always end in 1 and I know that the second IP address will always end in 20 How do I get the second address to calculate to to be always 20. The first 3 octets, although follow a formular across the 2000 sites will differ If anyone can help it would be most grateful I don;t fancy having to do it manually Sean... Hi Sean One way: =LEFT(A1,LEN(A1)-1)&20 -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formular to add to a IP address
=LEFT(A10,FIND("~",SUBSTITUTE(A10,".","~",3)))&20
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sean" wrote in message ... I know the default gateway of over 2000 routers. They are in a master spreadsheet which has created a vlookup from another sheet within the same workbook. If the default gateway is 100.100.100.1 in a second column I want to produce a ip address of 100.100.100.20. Once I have this I can copy the formular to make a third IP address of 100.100.100.25 I know that the last octet in the dafault gateway will always end in 1 and I know that the second IP address will always end in 20 How do I get the second address to calculate to to be always 20. The first 3 octets, although follow a formular across the 2000 sites will differ If anyone can help it would be most grateful I don;t fancy having to do it manually Sean... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formular to add to a IP address
worked a treat - thanks
Sean "Leo Heuser" wrote: "Sean" skrev i en meddelelse ... I know the default gateway of over 2000 routers. They are in a master spreadsheet which has created a vlookup from another sheet within the same workbook. If the default gateway is 100.100.100.1 in a second column I want to produce a ip address of 100.100.100.20. Once I have this I can copy the formular to make a third IP address of 100.100.100.25 I know that the last octet in the dafault gateway will always end in 1 and I know that the second IP address will always end in 20 How do I get the second address to calculate to to be always 20. The first 3 octets, although follow a formular across the 2000 sites will differ If anyone can help it would be most grateful I don;t fancy having to do it manually Sean... Hi Sean One way: =LEFT(A1,LEN(A1)-1)&20 -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formular to add to a IP address
thanks
Sean... "Bob Phillips" wrote: =LEFT(A10,FIND("~",SUBSTITUTE(A10,".","~",3)))&20 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sean" wrote in message ... I know the default gateway of over 2000 routers. They are in a master spreadsheet which has created a vlookup from another sheet within the same workbook. If the default gateway is 100.100.100.1 in a second column I want to produce a ip address of 100.100.100.20. Once I have this I can copy the formular to make a third IP address of 100.100.100.25 I know that the last octet in the dafault gateway will always end in 1 and I know that the second IP address will always end in 20 How do I get the second address to calculate to to be always 20. The first 3 octets, although follow a formular across the 2000 sites will differ If anyone can help it would be most grateful I don;t fancy having to do it manually Sean... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formular to add to a IP address
You're welcome.
Thanks for the feedback. Leo Heuser "Sean" skrev i en meddelelse ... worked a treat - thanks Sean "Leo Heuser" wrote: "Sean" skrev i en meddelelse ... I know the default gateway of over 2000 routers. They are in a master spreadsheet which has created a vlookup from another sheet within the same workbook. If the default gateway is 100.100.100.1 in a second column I want to produce a ip address of 100.100.100.20. Once I have this I can copy the formular to make a third IP address of 100.100.100.25 I know that the last octet in the dafault gateway will always end in 1 and I know that the second IP address will always end in 20 How do I get the second address to calculate to to be always 20. The first 3 octets, although follow a formular across the 2000 sites will differ If anyone can help it would be most grateful I don;t fancy having to do it manually Sean... Hi Sean One way: =LEFT(A1,LEN(A1)-1)&20 -- Best regards Leo Heuser Followup to newsgroup only please. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel email address hyperlink does not update | Excel Discussion (Misc queries) | |||
| How do I move excel address lists to outlook. and vice versa. | Excel Discussion (Misc queries) | |||
| How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
| How can I get an address in one cel and vertical? | Excel Discussion (Misc queries) | |||
| Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
Linear Mode
