Excel VBA Substring

Question

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

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

In Excel VBA, I can't seem to use string.substring (start, end). A Google search seems to show that Mid (line, start, end) is the best option. The problem is this: in the middle, the integer for the end is the distance from the beginning, not the actual location of the pointer. This means that different sizes of names will have different final locations, and I cannot use the index ")" to find the end of the region. Since all names begin with CN =, I can correctly find the end of the first substring, but I cannot find the ")" correctly, because the names are of different lengths.

I have the 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 how to actually use a dialing endpoint instead of an endpoint that has so many values ​​from the start?

Answer 1

This is vba .. no string.substring;)

this is more like VB 6 (or any of them below) .. so you are stuck with the middle, instr, len (to get the full length of the string). I think you skipped len to get the total number of characters in a string? If you need clarification, just send 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 a difference is required for the second parameter, you should always do some math ...

Answer 2

I'm not sure I answered your question correctly, but here is my implementation (hopefully) that 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 

This illustrates the use of the Split and Mid functions.

This is a quick and dirty implementation, serving only illustrative purposes. To use it in real code, you need to add a few checks (for example, that the kv and parts collections contain at least two elements).

UPD: To cover the two possible CN field formats, namely "last\, first (region)" and "first last (region)" and make things a little less messy, I would use a regex 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 

Answer 3

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

Here is what I hacked together quickly:

 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 combine them to get the desired line output.

Answer 4

The bit of the first / last last name is simple if you start with this:

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

Answer 5

Good tutorial. If someone is interested, to learn how to refuse zoominfo using VBA, please click on the link