IP ADDRESS MODIFICATION

  • Thread starter Thread starter calvin
  • Start date Start date
C

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
 
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
 
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.
 
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.
 
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)
 
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
 
Back
Top