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
--
Gary''s Student - gsnu200811


"calvin" wrote:

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

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.

"calvin" wrote:

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

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

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

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)


--
Gary''s Student - gsnu200811


"David Biddulph" wrote:

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

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

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


On Oct 31, 5:52*pm, calvin wrote:
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

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

"muddan madhu" wrote in message
...
try this

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


On Oct 31, 5:52 pm, calvin wrote:
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