Formular to add to a IP address

G

Guest

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...
 
L

Leo Heuser

Sean said:
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...
Click to expand...

Hi Sean

One way:

=LEFT(A1,LEN(A1)-1)&20
 
B

Bob Phillips

=LEFT(A10,FIND("~",SUBSTITUTE(A10,".","~",3)))&20

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

thanks
Sean...

Bob Phillips said:
=LEFT(A10,FIND("~",SUBSTITUTE(A10,".","~",3)))&20

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Static IP on a printer fails 6
A senior citizen needs help with a formular 7
Formatting text in a formular 5
what is the formular to use from one wokesheet to a new workbook 1
Formular call to access report 5
increment IP address. 1
Unable To Connect To Internet - IP Address Conflict 6
Find out when IP address was changed with Hosting Company 4

Top