IP ADDRESS MODIFICATION


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
 
Ad

Advertisements

G

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
 
J

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.
 
D

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.
 
G

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)
 
Ad

Advertisements

D

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
 

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

Similar Threads

IP Address Validation? 6
Excel IP addresses 5
Sorting by IP address?? 6
Sorting IP Address 12
sorting IP addresses 3
Formular to add to a IP address 5
splitting up an IP address 2
Sorting IP Addresses in Excel? 6

Top