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... |
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. |
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... |
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. |
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... |
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. |
All times are GMT +1. The time now is 03:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com