Parse substring from text

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

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 & " " & regionEnd FunctionSub 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 & " " & regionEnd FunctionSub 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

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 Stringtmp = "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=comandnew_string = Left(new_string, (InStr(1, new_string, ",") - 2))' John Smith (region)region = Right(new_string, Len(new_string) - InStr(1, new_string, "("))' regionnew_string = Left(new_string, (InStr(1, new_string, "(") - 2))' John Smithlast = Right(new_string, Len(new_string) - InStr(1, new_string, " "))' Smithfirst = Left(new_string, (InStr(1, new_string, " ") - 1))' John

Then concatenate them to get the string output you want.