Text between characters

Brief description:

Returns the text between specified characters – eg. specifying “fox” and “here” for “the fox jumped there” returns ” jumped t”

Full description:

Returns the text between specified strings of characters.

For example: Specifying “fox” and “here” for “the fox jumped there” returns ” jumped t”

To use: Call the function from a cell, specifying the string to work on and the starting and ending strings to use. ie using the example above, if the text “the fox jumped there” is in cell ‘G4’ – Use:

=TextBetweenChars(G4, “fox”, “here”)

Code:

Function TextBetweenChar(ByVal InString As String, DefChar As String) As String
    'Returns the string between the first and 2nd instance of the specified character
    '
    
    Dim FirstPos As Integer, SecPos As Integer
    Dim DefCharCount As Integer
    Dim CharInd As Integer
    
    DefCharCount = (Len(InString) - Len(Replace(InString, DefChar, ""))) / Len(DefChar)
    If DefCharCount < 2 Then
        TextBetweenChar = ""
        Exit Function
    End If
    
    FirstPos = Application.WorksheetFunction.Search(DefChar, InString, 1) + Len(DefChar)
    SecPos = Application.WorksheetFunction.Search(DefChar, InString, FirstPos + 1)
    
    TextBetweenChar = Mid(InString, FirstPos, SecPos - FirstPos)
    
End Function