Excel User Defined Function for String Split - Split an IP Address by Octet

I routinely find myself in a situation in Excel where I am having to use annoying string manipulation functions in order to extract a desired string from rows of text. Having some experience in Python and VBA the one function I end up missing the most is the split function. Simply put, this function breaks up a string by a specified delimiter and returns an array of the results. This proves to be very useful.

For example, say you want to break out the octets of multiple IP addreses. In order to accomplish this task in Excel you would have to use a combination of find() and mid() functions. Although possible, this is time consuming and often requires a bit of tuning before you get exactly what you want. Instead, try this UDF. 
'bcell     : A reference to a cell
'delimiter : a string containing the character(s) you would like to use to split the input string by
'index     : which item from the resulting array you would like to return
Function stringsplit(bcell As Range, delimiter As String, _
                     index As Integer) As String
On Error GoTo errHandler
Dim splitStr As Variant
'We want index to start from 1, not 0
'This is a more user friendly approach
If index = 0 Then
    GoTo errHandler
End If
index = index - 1
varSplit = Split(bcell.Value, delimiter)
StringSplit = varSplit(index)
Exit Function
errHandler:
    StringSplit = "#N/A"
End Function

Here are some examples how we can use this in Excel. Let's assume A1 has the value "192.168.1.254" and we want to break out individual octets of this IP address. 

=stringsplit( A1, ".", 1 )
=192

=stringsplit( A1, ".", 2 )
=168

---

If you're looking for something that is more specialized for handling IP addresses you may be interested in the below function. Usage is simply, =octet(A1,2) returns the second octet. The findIP() support function is used in case the text you are trying to parse contains other characters. 

'This function returns the value of a specified octet in an IP address
'address : IP address stored as a string e.g. "192.168.1.1"
'index   : integer value ranging from 1 to 4, specifying the octet to be returned
Function octet(address As String, index As Integer) As String
Dim addSplit As Variant
'Catch some typical error cases
If index = 0 Or index > 4 Or FindIP(address) = "" Then
    GoTo errHandler
End If
'Use this offset to index the array
index = index - 1
addSplit = Split(FindIP(address), ".")
Octet = Int(addSplit(index))
Exit Function
errHandler:
Octet = "#N/A"
End Function

'A support function that parses input strTest through a regular
'expression to find an IP Address
'A blank value is returned if no IP address can be found
Function FindIP(strTest As String) As String
    Dim RegEx As Object
    Dim valid As Boolean
    Dim Matches As Object
    Dim i As Integer
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Pattern = "\b(?:\d{1,3}\.){3}\d{1,3}\b"
    valid = RegEx.test(strTest)
    If valid Then
        Set Matches = RegEx.Execute(strTest)
        FindIP = Matches(0)
    Else
        FindIP = ""
    End If
End Function