解析文本中的子字符串 [英] Parse substring from text
问题描述
我正在编写一个宏,该宏将LDAP格式的名称列表转换为第一,最后(区域)".
对于那些不知道LDAP外观的用户,请参见以下内容:
CN = John Smith(地区),OU =法律,DC =示例,DC = comand
在Excel VBA中,我似乎无法使用string.substring(开始,结束).在Google上进行的搜索似乎表明,最好使用Mid(字符串,开始,结束).
问题是这样的:在Mid中,end的整数是距起点的距离,而不是字符的实际索引位置.这意味着不同的名称大小将具有不同的结束位置,并且我不能使用索引)"来查找区域的结尾.由于所有名称均以CN =开头,因此我可以正确找到第一个子字符串的末尾,但由于名称长度不同,因此无法正确找到)".
我下面有一些代码:
mgrSub1 = Mid(mgrVal, InStr(1, mgrVal, "=") + 1, InStr(1, mgrVal, "\") - 4)
mgrSub2 = Mid(mgrVal, InStr(1, mgrVal, ","), InStr(1, mgrVal, ")") - 10)
manager = mgrSub1 & mgrSub2
是否有一种方法可以使用设置的终点,而不是距离起点有那么多值的终点?
这是vba .. no string.substring;)
这更像是VB 6(或下面的任何一个)..因此,您陷入了mid,instr,len(获取字符串总len)的问题.我想您错过了len以获取字符总数一串?如果您需要澄清,请发表评论.
另一个快速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
很明显,您的问题是,由于您需要对第二个参数进行区分,因此应始终对其进行一些数学运算.
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?
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...
这篇关于解析文本中的子字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!