Excel Formula

[Guest]

Would like to set up a formula that will take an IP address input by user ie.
10.252.45.14 Then I want to take that IP and be able to add 1 or subtract 2
from the last set of Numbers ##.###.##.14 and place the output to another
cell. Tried a couple formulas but error out. Any suggestions be great....

Thanks

 

[T. Valko]

What if the address was:

10.252.45.001

How would you subtract 2 from the last set of digits?

Biff

 

[David Biddulph]

As easy as way as any might be to use Data/ text to columns, & use the full
stop as delimiter.
You can then do what manipulation you want on the last segment.
If you want to glue the parts back together you can use:
=A1&"."&B1&"."&C1&"."&D1

 

[RagDyer]

This would have to be done using code because there are just too many
variables to be covered by functions alone.

Perhaps you don't realize that the maximum value in each octet is 255,
therefore, adding 1 to say:
125.12.119.255
would result in
125.12.120.0

Therefore, adding 1 to
125.255.255.255
would result in
126.0.0.0

Subtraction would encounter the same type of complicated computation.

Post to the programming group for help.

 

[Roger Govier]

Hi

Provided you were not wishing to deal with the complications of moving
outside of the 255 max value in the last set, as discussed by RD in his
posting, then the following will work for adding 1 to the last set

=IF(--MID(F1,FIND("^",SUBSTITUTE(F1,".","^",3))+1,3)<=254,
LEFT(F1,FIND("^",SUBSTITUTE(F1,".","^",3)))&
MID(F1,FIND("^",SUBSTITUTE(F1,".","^",3))+1,3)+1,"Error")

Similarly to subtract 2
=IF(--MID(F1,FIND("^",SUBSTITUTE(F1,".","^",3))+1,3)>=2,
LEFT(F1,FIND("^",SUBSTITUTE(F1,".","^",3)))&
MID(F1,FIND("^",SUBSTITUTE(F1,".","^",3))+1,3)-2,"Error")