Solved

# Excel: Get third octet of an IP address

Posted on 2010-11-24
3,106 Views
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

LVL 30

Expert Comment

ID: 34204306
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

LVL 50

Expert Comment

ID: 34204350
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

LVL 50

Expert Comment

ID: 34204403
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

LVL 58

Accepted Solution

cyberkiwi earned 500 total points
ID: 34204425
=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

LVL 58

Expert Comment

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

LVL 50

Expert Comment

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

LVL 50

Expert Comment

ID: 34204646
Try

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

regards, barry
0

LVL 50

Expert Comment

ID: 34204854
...or....

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

barry
0

Author Comment

ID: 34204871
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 Closing Comment

ID: 34204914
Had to modify the suggestion due to regional differences between the two computers.
0

LVL 58

Expert Comment

ID: 34207675
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

### Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…