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 to to
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


If you have do you want it changed to 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:
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)



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 becomes :-(
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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question