Parse substring from text
Solution 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...
Solution 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
Solution 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.
spassen
Updated on December 09, 2020Comments
-
spassen over 3 years
I am writing a macro that converts a list of names that are in an LDAP format to First, Last (region).
For those who 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
Is there a way to use a set end point instead of an end point that is so many values away from the start?
-
spassen about 12 yearsThe total number of chars in the string won't help me. I the length can vary on either side of the ")" so I cannot find the index location to stop based on that.
-
gbianchi about 12 yearscan you post an example of the output you need? usually you have to code a for loop to accomplish this
-
spassen about 12 yearsCN=Smith, John (region),OU=Legal,DC=example,DC=comand should become Smith, John (region)
-
spassen about 12 yearsThis looks like it should work, but when I try GetName(CN=last\, first (AAA-somewhere),OU=IT,OU=Users,OU=somewhere - aaa,OU=place,DC=aaa,DC=com) I get an error subscript out of range at Value = kv(1) on its second time through the loop.
-
Gebb about 12 years@spassen, I didn't know there could be commas (escaped) in the name. This will complicate the code.
-
spassen about 12 yearsDon't know why I didn't think of using math. That should have been obvious. Guess it's been a long day. Thanks.
-
spassen about 12 yearsI actually figured it out. I completely forgot I could do end - start. It's been a long day. Thanks for the help though. That's a useful function for future use.