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:
Code:
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 Else 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