Excel: Get third octet of an IP address

Posted on 2010-11-24
Last Modified: 2012-05-10
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 (.).


I should get the following:

Thank you :)
Question by:Advizor
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
LVL 30

Expert Comment

ID: 34204306
Would this help?
Sub Sample()
    Dim strIP As String, MyArray() As String
    strIP = ""
    MyArray = Split(strIP, ".")
    MsgBox MyArray(2)
End Sub

Open in new window

LVL 50
ID: 34204350
Here's a formula suggestion:


start in row 1 and copy down

cheers, teylyn
LVL 50
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

Open in new window

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


to calculate the result.
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

LVL 58

Accepted Solution

cyberkiwi earned 500 total points
ID: 34204425

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

Expert Comment

ID: 34204451
Nevermind, please ignore the previous comment. Been working at it too long - there are better answers.
LVL 50
ID: 34204475
CK, nice one with the TRUNC. It does work for my data sample, so no need to apologize.
LVL 50

Expert Comment

by:barry houdini
ID: 34204646


regards, barry
LVL 50

Expert Comment

by:barry houdini
ID: 34204854



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

Author Closing Comment

ID: 34204914
Had to modify the suggestion due to regional differences between the two computers.
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.


Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question