VBA certain Part of a String

Question

I have an excel column with strings like this one: ABC-1234-GLK-1234-10TH8E10-21 71-D I need to give to a variable the value GL-1234 (the substring between the 2nd dash and the 4th). I tried

x=Mid(string, 10, 8)

but the problem is that not all the strings have the same length, however the only constant is that the substring that i want is between the 2nd and the 4th dash.

Any ideas?

Answer 1

Split the string and extract what you want:

Dim dataSplit() As String
Dim dataString As String

dataSplit = Split(Sheet1.Range("C14").Value2, "-")

dataString = dataSplit(2) & "-" & dataSplit(3)

Answer 2

You can try the following, although I don't think it is the most elegant or the easiest solution:

x = ""
p1 = InStr(1, string, "-")
If p1 <> 0 Then
    p2 = InStr(1 + p1, string, "-")
    If p2 <> 0 Then
        p3 = InStr(1 + p2, string, "-")
        If p3 <> 0 Then
            p4 = InStr(1 + p3, string, "-")
            If p4 <> 0 Then
                x = Mid(string, p2 + 1, p4 - p2 - 1)
            End If
        End If
    End If
End If