从文本中解析子字符串 [英] Parse substring from text

问题描述

我正在编写一个宏，它将 LDAP 格式的名称列表转换为 First、Last(区域).

I am writing a macro that converts a list of names that are in an LDAP format to First, Last (region).

对于那些不知道 LDAP 是什么样子的人，它如下:

For those who do not know what LDAP looks like, it is below:

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

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

在 Excel VBA 中，我似乎无法使用 string.substring(start, end).在 Google 上搜索似乎表明 Mid(string, start, end) 是最好的选择.

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.

问题是这样的:在 Mid 中，end 的整数是与 start 的距离，而不是字符的实际索引位置.这意味着不同的名称大小将有不同的结束位置，我不能使用)"的索引来查找区域的结尾.由于所有名称都以 CN= 开头，因此我可以正确找到第一个子字符串的结尾，但我无法正确找到)"，因为名称的长度不同.

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.

下面有一些代码:

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?

推荐答案

这是 vba.. 没有 string.substring ;)

This is vba.. no string.substring ;)

这更像是 VB 6(或以下任何一个).. 所以你被 mid, instr, len 困住了(得到一个字符串的总长度).. 我想你错过了 len 来得到字符的总数在一个字符串中?如果您需要澄清，请发表评论.

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.

另一个快速破解..

    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屋！