VBA Excel – String between two strings

Today I was looking for a solution, giving me the option to locate a string, defined between two strings. Pretty much the story of any developer’s life.

So, I found a really useful resource, which helped me to build my code – http://www.devx.com/tips/Tip/40934

I just amended the possibility that we may have some paragraphs in front and in the back, which really changes the solution, as far as I do not want paragraphs returned as a result. So, as you see, the code is actually copy+paste from the source above, I have just added two additional optional arguments, helping me to decide whether I want to have the paragraph from the start or the end removed. The paragraph is with 2 signs, that’s why I remove 2 in the conditional statements in the middle.

Public Function GetBetween(ByRef sSearch As String, ByRef sStart As String, ByRef sStop As String, _
                           Optional ByRef lSearch As Long = 1, Optional ByRef hasParagraph As Boolean, Optional ByRef HasParagraphBefore As Boolean) As String
    lSearch = InStr(lSearch, sSearch, sStart)
    If lSearch > 0 Then
        lSearch = lSearch + Len(sStart)
        Dim lTemp As Long
        lTemp = InStr(lSearch, sSearch, sStop)

        If lTemp > lSearch Then
            If hasParagraph Then
                GetBetween = Mid$(sSearch, lSearch, lTemp - lSearch - 2)
            Else
                GetBetween = Mid$(sSearch, lSearch, lTemp - lSearch)
            End If

            If HasParagraphBefore Then
                GetBetween = Right(GetBetween, Len(GetBetween) - 2)
            End If
        End If
    End If

End Function

At the end I had to develop something like a get last method, giving me the end of the string. So, this is it, all developed by myself  – GetLast:

Public Function getLast(ByRef sTextForEdit As String, ByRef sTempText As String) As String
    Dim lPosition As Long

    lPosition = InStr(sTextForEdit, sTempText)
    getLast = Right(Mid(sTextForEdit, lPosition), Len(Mid(sTextForEdit, lPosition)) - Len(sTempText))

End Function

And it worked out awfully beautiful!

🙂