• Status: Solved
• Priority: Medium
• Security: Public
• Views: 3720

# Excel: Get third octet of an IP address

Hello everybody

Can you guys help me find a way to get the third octet from an IP address?
What i basically need is everything that is in between (but not including) the second and third dot (.).

Examples:

192.168.12.77
172.16.215.33
10.0.1.1

I should get the following:
12
215
1

Thank you :)
0
• 3
• 3
• 2
• +2
1 Solution

Commented:
Would this help?
``````Sub Sample()
Dim strIP As String, MyArray() As String

strIP = "192.168.12.77"

MyArray = Split(strIP, ".")

MsgBox MyArray(2)

End Sub
``````
0

Microsoft MVP ExcelCommented:
Here's a formula suggestion:

=LEFT(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,99),FIND(".",MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,99))-1)

start in row 1 and copy down

cheers, teylyn
0

Microsoft MVP ExcelCommented:
Building on SiddhartRout's VBA suggestion: if you want a dynamic VBA solution, you could try this user defined formula

``````Function Sample(strIP As String)
Dim MyArray() As String
MyArray = Split(strIP, ".")
Sample = MyArray(2)
End Function
``````

Copy and paste the code into a regular code module and then use

=sample(A1)

to calculate the result.
0

Commented:
=TRUNC("0"&MID(A1,FIND("|",SUBSTITUTE(A1&"..",".","|",2))+1,10))

Won't get any #value #n/a errors if it is not a valid ip address, 0 instead
0

Commented:
Nevermind, please ignore the previous comment. Been working at it too long - there are better answers.
0

Microsoft MVP ExcelCommented:
CK, nice one with the TRUNC. It does work for my data sample, so no need to apologize.
0

Commented:
Try

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,".",REPT(" ",9)),18),9))

regards, barry
0

Commented:
...or....

=INT(-LOOKUP(0,-RIGHT(A1,{1,2,3,4,5,6,7})))

barry
0

Author Commented:
Thank you all for your suggestions.

I've managed to get something working from cyberkiwi's formula.
I ran into issues regarding international/regional settings when trying to use an unmodified version of cyberkiwi's.
Truncate expects a number, but on my danish version of Excel it does not see the preliminary result (on which to truncate) as a number. Yay! :/

Here is what I got to work:

=MID(B9;FIND("|";SUBSTITUTE(B9 & "..";".";"|";2))+1;FIND("|";SUBSTITUTE(B9 & "..";".";"|";3))-FIND("|";SUBSTITUTE(B9 & "..";".";"|";2))-1)

Thank you cyberkiwi (and all others as well) :)
0

Author Commented:
Had to modify the suggestion due to regional differences between the two computers.
0

Commented:
Though I am happy for the points, I thought the other formulas worked as well.
Barry has a knack for formulaes, and his two options are both amazing pieces of art.

While the 2nd one returns the wrong result when the fourth octet is 0, I think the 1st one works.

Regards
0

## Featured Post

• 3
• 3
• 2
• +2
Tackle projects and never again get stuck behind a technical roadblock.