Text before character

Brief description:

Returns the text before the specified character or the nth instance of the specified character

Full description:

Returns the text upto the specified character(s). For example:

=TextbeforeChar(“the-fox-jumped-there”, “there”)

would return “the-fox-jumped”

Using the optional 3rd argument allows the user to select the nth instance of the character. For example:

=TextbeforeChar(“the-fox-jumped-there”, “-“, 2) would return “the-fox” as shown below:


VBA code here…

Function TextBeforeChar(ByVal InString As String, DefChar As String, Optional InstNum As Integer) As String
    'Returns the string before the specified char(s)
    '   Allows an optional argument to specify the instance of the specified character.
    '   if the character is not found in the string, the original string is returned.
    Dim FirstPos As Integer, CurrPos As Integer
    Dim RetString As String
    Dim DefCharCount As Integer
    Dim CharInd As Integer
    If InstNum = 0 Then
        FirstPos = Application.WorksheetFunction.Search(DefChar, InString, 1) - 1
        TextBeforeChar = Mid(InString, 1, FirstPos)
        Exit Function
        DefCharCount = (Len(InString) - Len(Replace(InString, DefChar, ""))) / Len(DefChar)
        If InstNum > DefCharCount Then
            TextBeforeChar = InString
            Exit Function
        End If
        CurrPos = 1
        For CharInd = 1 To InstNum
            CurrPos = Application.WorksheetFunction.Search(DefChar, InString, CurrPos + 1)
        Next CharInd
        CurrPos = CurrPos - 1
        TextBeforeChar = Mid(InString, 1, CurrPos)
    End If

End Function