Excel VBA Substring

问题:

I am writing a macro that takes a list of names that are in an LDAP format converts them to First, Last (region). For those of you that do not know what LDAP looks like, it is below:

CN=John Smith (region),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. 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.

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

Does anyone know of a way to actually use a set end point instead of an end point that is so many values away from the start?

回答1:

This is vba.. no 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.

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...

回答2:

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 Split and Mid functions.

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 kv and parts collections contain at least two elements).

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.

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

回答3:

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

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.

回答4:

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

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