Changing 3rd octet in an IP address

**ReeceB** · 01-21-2013, 06:58 PM

Greetings from a newbie.

I have a need to be able to separate the 3rd octet in an IP address so I can increment it by one and then change the last octet. This is for automatically setting a default gateway for a /23 subnet mask.
For example...

10.2.16.2 would become 10.2.17.254
192.168.10.2 would become 192.168.11.254

Octets may be made up of 1-3 digits.

Any help is appreciated.

ReeceB...

**Ron Coderre** · 01-21-2013, 07:17 PM

With your sample data in A1:A2
A1: 10.2.16.2
A2: 192.168.10.2

This regular formula transforms that string by:
...incrementing the 3rd section by 1
...replacing the 4th section with 254
B1: =SUBSTITUTE(LEFT(SUBSTITUTE(A1,".","."&REPT(" ",LEN(A1))),LEN(A1)*2)&MID(SUBSTITUTE(A1,".","."&REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1))+1&".254"," ","")
Copy that formula into B2

The end results will be:
10.2.17.254
192.168.11.254

Is that something you can work with?

**JieJenn** · 01-21-2013, 07:29 PM

I did a similar task in the past, I think for my take I'd probably go with VBA.

Public Function UpdateIP(ByVal strIP As String) As String
Dim i As Integer, x As Integer
Dim strLeft As String, strMid As String

x = 0
For i = 1 To Len(strIP)
    If Mid(strIP, i, 1) = "." Then
        x = x + 1
        If x = 2 Then
            strLeft = Left(strIP, i)
        ElseIf x = 3 Then
            strMid = Mid(strIP, Len(strLeft) + 1, i - Len(strLeft) - 1)
            strMid = strMid + 1
        End If
    End If
Next i

UpdateIP = strLeft & strMid & ".254"
    
End Function

**ReeceB** · 01-21-2013, 07:31 PM

Ron, you are an absolute champion.

Even a simple person like me can work with that. Works a treat.

Thanks heaps for your quick response. It has saved what little hair I have left.

Reece...