Changing IP Addresses

[t2true]

I need to change the IP addresses of some servers to a different network, but I want to retain the last octet. They are in the 192.168.1.0 network and need to be changed to the 10.0.0.0 network.

How can I either replace the first three octets or extract the last octet in order to the change the IPs? (Each octet may be different character lengths.)

192.168.1.120 >> 10.0.0.120
192.168.1.143 >> 10.0.0.143
192.168.1.92 >> 10.0.0.92
192.168.1.4 >> 10.0.0.4
192.168.1.5 >> 10.0.0.5

Thanks

 

[Joe4]

Try this, for an entry in cell A1:

="10.0.0." & TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",100)),100))

 

[t2true]

That worked perfect! Thank you.

If not too much trouble, any way you can explain how it works so I understand it?

 

[Joe4]

You are welcome.

It is easiest to follow if we break this into two parts. First, we are taking the hard-coded string "10.0.0."

In the second part, we are replacing each period in thr original value with 100 spaces.
Then we are taking the right most 100 characters of that, which will give us our last value after the last period, and a bunch of spaces.
The TRIM function removes all the spaces, so we are just left with the value.

Then we just combine parts one and two.

 

[t2true]

Excellent! Thank you very much.