Excel Formula
 

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

Excel Formula
 

What if the address was:

10.252.45.001

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

Biff

"fritzer6" wrote in message
...
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

Excel Formula
 

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
--
David Biddulph

"fritzer6" wrote in message
...
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

Excel Formula
 

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"fritzer6" wrote in message
...
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

Excel Formula
 

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)<=25 4,
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")

--
Regards

Roger Govier


"fritzer6" wrote in message
...
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