I am trying to set up a formula to show a subsequent IP Address.

J

jewell

I am trying to set up a formula where i can enter an IP address and have that
the cell below that shows that IP adress + 1. The problem is the decimal
points in the 1 st ip address. It's causing a value error. Any idea how to
do what i'm attempting?
 
G

Glenn

jewell said:
I am trying to set up a formula where i can enter an IP address and have that
the cell below that shows that IP adress + 1. The problem is the decimal
points in the 1 st ip address. It's causing a value error. Any idea how to
do what i'm attempting?


With no error checking for valid IP addresses:

=LEFT(SUBSTITUTE(A1,".","*",3),FIND("*",SUBSTITUTE(A1,".","*",3))-1)&
"."&TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",9)),9))+1
 
G

Glenn

jewell said:
I am trying to set up a formula where i can enter an IP address and have that
the cell below that shows that IP adress + 1. The problem is the decimal
points in the 1 st ip address. It's causing a value error. Any idea how to
do what i'm attempting?


Shortened version:

=LEFT(A1,FIND("*",SUBSTITUTE(A1,".","*",3))-1)&"."&
TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",9)),9))+1
 

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

Top