VBA字符串的某些部分

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

我有一个像这样的字符串的excel列：ABC-1234-GLK-1234-10TH8E10-21 71-D我需要给变量赋值GL-1234（第二个破折号和第4个之间的子字符串）。我试过了

x=Mid(string, 10, 8)

x =中（字符串，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.

但问题是并非所有字符串都具有相同的长度，但唯一的常量是我想要的子字符串位于第2和第4个短划线之间。

Any ideas?

有任何想法吗？

3 个解决方案

#1

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

#2

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)

#3

this will return 2nd dash and 4th dash, B1 is string : ABC-1234-GLK-1234

这将返回第二个破折号和第四个破折号，B1是字符串：ABC-1234-GLK-1234

=MID(B1,FIND("-",B1,FIND("-",B1)+1),FIND("-",B1,FIND("-",B1,FIND("-",B1,FIND("-",B1)+1)+1)+1) - FIND("-",B1,FIND("-",B1)+1))

#1