vba 从文本中解析子字符串

Parse substring from text

提问by spassen

I am writing a macro that converts a list of names that are in an LDAP format to First, Last (region).

我正在编写一个宏，将 LDAP 格式的名称列表转换为 First, Last (region)。

For those who do not know what LDAP looks like, it is below:

对于那些不知道 LDAP 是什么样子的人，它如下：

CN=John Smith (region),OU=Legal,DC=example,DC=comand

CN=John Smith（地区）,OU=Legal,DC=example,DC=comand

In Excel VBA, I do not appear to be able to use string.substring(start, end). A search on Google seems to reveal that Mid(string, start, end) is the best option.

在 Excel VBA 中，我似乎无法使用 string.substring(start, end)。在 Google 上的搜索似乎表明 Mid(string, start, end) 是最佳选择。

The problem is this: In Mid, the integer for end is the distance from start, not the actual index location of the character. This means that different name sizes will have different ending locations and I cannot use index of ")" to find the end of the region. Since all of the names start with CN=, I can find the end of the first substring properly, but I cannot find ")" properly because names are different lengths.

问题是这样的：在 Mid 中，end 的整数是距开始的距离，而不是字符的实际索引位置。这意味着不同的名称大小会有不同的结束位置，我不能使用“)”的索引来查找区域的结尾。由于所有名称都以 CN= 开头，因此我可以正确找到第一个子字符串的结尾，但由于名称长度不同，我无法正确找到“)”。

I have some code below:

我在下面有一些代码：

mgrSub1 = Mid(mgrVal, InStr(1, mgrVal, "=") + 1, InStr(1, mgrVal, "\") - 4)
mgrSub2 = Mid(mgrVal, InStr(1, mgrVal, ","), InStr(1, mgrVal, ")") - 10)
manager = mgrSub1 & mgrSub2

Is there a way to use a set end point instead of an end point that is so many values away from the start?

有没有办法使用设定的终点而不是距离起点有很多值的终点？

回答by gbianchi

This is vba.. no string.substring ;)

这是 vba .. 没有 string.substring ;)

this is more like VB 6 (or any one below).. so you are stuck with mid, instr, len (to get the total len of a string).. I think you missed len to get the total of chars in a string? If you need some clarification just post a comment.

这更像是 VB 6（或下面的任何一个）。所以你坚持使用 mid、instr、len（以获得字符串的总长度）。我认为你错过了 len 以获得字符串中的字符总数? 如果您需要一些说明，请发表评论。

edit:

编辑：

Another quick hack..

另一个快速黑客..

    Dim t As String
    t = "CN=Smith, John (region),OU=Legal,DC=example,DC=comand"
    Dim s1 As String
    Dim textstart As Integer
    Dim textend As Integer
    textstart = InStr(1, t, "CN=", vbTextCompare) + 3
    textend = InStr(1, t, "(", vbTextCompare)
    s1 = Mid(t, textstart, textend - textstart)
    MsgBox s1
    textstart = InStr(1, t, "(", vbTextCompare) + 1
    textend = InStr(1, t, ")", vbTextCompare)
    s2 = Mid(t, textstart, textend - textstart)
    MsgBox s2

Clearly your problem is that since you need a diference for the second parameter, you should always do some math for it...

显然您的问题是，由于您需要对第二个参数进行差分，因此您应该始终对其进行一些数学运算...

回答by Gebb

I'm not sure I got your question right, but here is my implementation of (hopefully) what you want:

我不确定我的问题是否正确，但这是我（希望）你想要的实现：

Function GetName(arg As String) As String
    parts = Split(arg, ",")
    For Each p In parts
        kv = Split(p, "=")
        Key = kv(0)
        Value = kv(1)
        If Key = "CN" Then
            commonName = Value
        End If
    Next p
    regIndex = InStr(1, commonName, "(")
    region = Mid(commonName, regIndex, Len(commonName) - regIndex + 1)
    parts = Split(commonName, " ")
    first = parts(0)
    last = parts(1)
    GetName = first & ", " & last & " " & region
End Function


Sub test()
 'Prints "John, Smith (region)"
 Debug.Print GetName("CN=John Smith (region),OU=Legal,DC=example,DC=comand")
End Sub

It illustrates the use of Splitand Midfunctions.

它说明了使用Split和Mid功能。

It is a quick and dirty implementation serving illustrative purposes only. To use it in real code you need to add several checks (e.g. that the kvand partscollections contain at least two elements).

这是一个快速而肮脏的实现，仅用于说明目的。要在实际代码中使用它，您需要添加几个检查（例如，kv和parts集合至少包含两个元素）。

UPD:To cover two possible formats of the CN field, namely "last\, first (region)"and "first last (region)"and make things a little less messy I would take the regular expressions approach.

UPD：要覆盖CN域的两种可能的格式，分别是"last\, first (region)"和"first last (region)"使事情少一点凌乱我会采取正则表达式的办法。

Function GetName(arg As String) As String
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "CN=(\w+)\s*?(\,)?.*?,"
    End With
    Set REMatches = RE.Execute(arg)
    If REMatches.Count < 1 Then
        GetName = ""
        Return
    End If

    cn = REMatches(0).Value
    withComma = (InStr(1, cn, "\,") > 0)
    If withComma Then
        lastIndex = 0
        firstIndex = 2
        regionIndex = 3
        patt = "(\w+)\s*?(\,)?\s*?(\w+)\s*(\(.*?\))"
    Else
        lastIndex = 1
        firstIndex = 0
        regionIndex = 2
        patt = "(\w+)\s*?(\w+)\s*(\(.*?\))"
    End If
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = patt
    End With
    Set REMatches = RE.Execute(arg)
    If REMatches.Count < 1 Then
        GetName = ""
        Return
    End If

    Set m = REMatches(0)
    first = m.SubMatches(firstIndex)
    last = m.SubMatches(lastIndex)
    region = m.SubMatches(regionIndex)
    GetName = first & ", " & last & " " & region
End Function


Sub test()
 ' Prints "first, last (AAA-somewhere)" two times.
 Debug.Print GetName("CN=last\, first (AAA-somewhere),OU=IT,OU=Users,OU=somewhere - aaa,OU=place,DC=aaa,DC=com")
 Debug.Print GetName("CN=first last (AAA-somewhere),OU=IT,OU=Users,OU=somewhere - aaa,OU=place,DC=aaa,DC=com")
End Sub

回答by Todd

I would use InStr to find the position of the three characters that separate the values and then use Left/Right on them.

我会使用 InStr 来找到分隔值的三个字符的位置，然后在它们上使用 Left/Right。

This is what I hacked together real quick:

这就是我快速破解的内容：

Dim tmp, new_string, first, last, region As String

tmp = "CN=John Smith (region),OU=Legal,DC=example,DC=comand"

new_string = Right(tmp, Len(tmp) - 3)
' John Smith (region),OU=Legal,DC=example,DC=comand
new_string = Left(new_string, (InStr(1, new_string, ",") - 2))
' John Smith (region)

region = Right(new_string, Len(new_string) - InStr(1, new_string, "("))
' region
new_string = Left(new_string, (InStr(1, new_string, "(") - 2))
' John Smith

last = Right(new_string, Len(new_string) - InStr(1, new_string, " "))
' Smith
first = Left(new_string, (InStr(1, new_string, " ") - 1))
' John

Then concatenate them to get the string output you want.

然后连接它们以获得您想要的字符串输出。

回答by Excel Hero

The first/last name bit is easy if you start with this:

如果你从这个开始，名字/姓氏位很容易：

MsgBox Split(Mid$(sLDAP, 4), ")")(0) & ")"