IP ADDRESS MODIFICATION

[calvin]

I have a ton (546) of ip address and i need to change just the last digit so
there wont be a conflic when i try connecting to the network

ex 10.191.168.223 to 10.191.168.224
10.14.129.10 to 10.14.129.11
and the list goes on an on
help please

 

[Gary''s Student]

Try this small user defined function:

Function IP_Bumper(r As Range) As String
n = Split(r.Value, ".")
n(UBound(n)) = n(UBound(n)) + 1
IP_Bumper = Join(n, ".")
End Function

 

[Joel]

If you have 10.14.129.19 do you want it changed to 10.14.129.20? It makes a
diffferrent on which solution to use. Would you be interested in a UDF
function which? The UDF would be much simplier to enter on the worksheet.

 

[David Biddulph]

I would be tempted to use Data/ Text to columns to split using the full stop
as delimiter, then use Edit/ Paste special/ Add to add 1 to the last column,
then a concatenation formula to glue the parts back together again.
Obviously you'll need to deal with any that go out of range, such as 255
going to 256 in IPv4.

 

[Gary''s Student]

Great idea!

In B1 enter:
=SEARCH("|",SUBSTITUTE(A1,".","|",(LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))
this gives the position of the laster period in the address

then in C1 enter:
=LEFT(A1,B1) & --RIGHT(A1,LEN(A1)-B1)+1

This re-composes the IP address with the last element incremented.

(this does not take care of the 255 thing)

 

[muddan madhu]

try this

=MID(A1,1,LEN(A16)-1)&RIGHT(A1,1)+1

 

[David Biddulph]

I assume that the A16 should be A1?

The problem with that is that any number ending with 9 will change 9 to 10,
hence 99.99.99.99 becomes 99.99.99.910 :-(
You could, of course trap for that, but it gets rather complicated depending
on whether or not you've got a 9 preceding the final 9.
--
David Biddulph

try this

=MID(A1,1,LEN(A16)-1)&RIGHT(A1,1)+1