vba 如何仅返回字符串中逗号后的文本

How to return only text after a comma in a string

提问by HappyNanaMO

I am extracting text between parens in the active window title bar. That part is working great (thanks to some help I received here previously!). Now I want to create two separate macros - one that returns only the first name, and the other that returns only the last name.

我正在活动窗口标题栏中的括号之间提取文本。这部分工作得很好（感谢我之前在这里收到的一些帮助！）。现在我想创建两个单独的宏 - 一个只返回名字，另一个只返回姓氏。

My active window title bar looks something like this:

我的活动窗口标题栏看起来像这样：

some text to the left (HENDERSON,TOM) some text to the right (there is no space after the comma)

左边的一些文本 (HENDERSON,TOM) 右边的一些文本（逗号后没有空格）

The last name macro works perfectly. It looks like this:

姓氏宏完美运行。它看起来像这样：

Sub a1LastName()
    'Extract last name of patient from title bar (between parens)
    Dim strPatientName As String
    Dim OpenPosition As Integer '(open paren marker)
    Dim closeposition As Integer '(close paren marker)
    OpenPosition = InStr(ActiveDocument.ActiveWindow.Caption, "(")
    closeposition = InStr(ActiveDocument.ActiveWindow.Caption, ")")
    strPatientName = Mid(ActiveDocument.ActiveWindow.Caption, _
        OpenPosition + 1, closeposition - OpenPosition - 1)
    Dim c As Long
    c = InStr(strPatientName, ",")
    strPatientName = Left(strPatientName, c - 1)
    Selection.TypeText strPatientName
End Sub

The second macro is identical to the first, except that the second-to-last line of code has a "Right" instead of a "Left" instruction:

第二个宏与第一个相同，除了倒数第二行代码有一个“右”而不是“左”指令：

Sub a1FirstName()
    'Extract first name of patient from title bar (between parens)
    Dim strPatientName As String
    Dim OpenPosition As Integer '(open paren marker)
    Dim closeposition As Integer '(close paren marker)
    OpenPosition = InStr(ActiveDocument.ActiveWindow.Caption, "(")
    closeposition = InStr(ActiveDocument.ActiveWindow.Caption, ")")
    strPatientName = Mid(ActiveDocument.ActiveWindow.Caption, _
        OpenPosition + 1, closeposition - OpenPosition - 1)
    Dim c As Long
    c = InStr(strPatientName, ",")
    strPatientName = Right(strPatientName, c - 1)
    Selection.TypeText strPatientName
End Sub

Here's my problem: The "first name" macro always returns the last name minus the first four characters, followed by the first name, instead of simply the first name.

这是我的问题：“名字”宏总是返回姓氏减去前四个字符，然后是名字，而不是简单的名字。

The only examples I'm able to find anywhere in Google land are specifically for Excel. I have combined through my VBA manuals, and they all give similar examples as I have used for extracting the text to the right of a character.

我能够在 Google 土地上的任何地方找到的唯一示例是专门针对 Excel 的。我已经结合了我的 VBA 手册，它们都给出了与我用来提取字符右侧的文本类似的示例。

What am I doing wrong?

我究竟做错了什么？

回答by Tim Williams

You can use Split()to create an array out of the comma-separated parts of the text, then access either the first or second part:

您可以使用Split()文本的逗号分隔部分创建一个数组，然后访问第一部分或第二部分：

Sub a1LastName()
    Dim strPatientName As String
    strPatientName = ParensContent(ActiveDocument.ActiveWindow.Caption)
    If strPatientName Like "*,*" Then
        Selection.TypeText Trim(Split(strPatientName, ",")(0))
    End If
End Sub


Sub a1FirstName()
    Dim strPatientName As String
    strPatientName = ParensContent(ActiveDocument.ActiveWindow.Caption)
    If strPatientName Like "*,*" Then
        Selection.TypeText Trim(Split(strPatientName, ",")(1))
    End If
End Sub

'Utility function: find and return text enclosed by ()
'   Return empty string if no () found
Function ParensContent(txt) As String
    Dim rv As String, pos As Long, pos2 As Long
    If txt Like "*(*)*" Then
        pos = InStr(1, txt, "(")
        pos2 = InStr(pos, txt, ")")
        rv = Mid(txt, pos + 1, (pos2 - pos) - 1)
    End If
    ParensContent = rv
End Function

回答by Byron Wall

Seems like everyone has keyed in on Splitto get the first/second part of the name. You can also use Splitto get rid of the parentheses. This works if you know that you will only (and always) have a single (and ).

似乎每个人都输入Split了名称的第一/第二部分。您也可以使用Split来去掉括号。如果您知道您只会（并且总是）有一个(和).

Codegives the main idea. You can use Splitto get the part of the Stringwhich does not include the (or )and then do it again to get either side of the ,.

代码给出了主要思想。您可以使用Split来获取String不包含(or 的部分)，然后再次执行以获取,.

Sub t()

    Dim str As String
    str = "(Wall,Byron)"

    Dim name_first As String
    Dim name_last As String

    'two splits total
    name_last = Split(Split(str, "(")(1), ",")(0)
    name_first = Split(Split(str, ")")(0), ",")(1)

    'three split option, same code to remove parentheses
    name_last = Split(Split(Split(str, "(")(1), ")")(0), ",")(0)
    name_first = Split(Split(Split(str, "(")(1), ")")(0), ",")(1)


End Sub

The code above presents a two Splitand a three Splitoption. The main difference is that the three Split variety uses the same code to strip off the parentheses, and the only change is which side of the ,to grab. The two Split options take advantage of the fact that splitting on a comma removes one the parentheses for free. The indices there are a little more complicated.

上面的代码提供了一个two Split和一个three Split选项。主要区别在于三个Split变种使用相同的代码去掉括号，唯一的变化是,抓取的哪一边。两个拆分选项利用了这样一个事实，即在逗号上拆分会免费删除一个括号。那里的索引稍微复杂一些。

回答by David G

EDIT it seems I didn,t understand well the first time. Attempt 2:

编辑似乎我第一次没有很好理解。尝试 2：

You want to remove everything right of the comma.

您想删除逗号右侧的所有内容。

Mystr = Left(Mystr,  Instr(Mystr, ","))
Mystr = Mid(Mystr, 2)

the second line uses mid to take everything after the first char (the parentesis)

第二行使用 mid 来获取第一个字符之后的所有内容（括号）

---

---

You're looking for the Mid function.

您正在寻找 Mid 功能。

http://www.excel-easy.com/vba/string-manipulation.html#mid

http://www.excel-easy.com/vba/string-manipulation.html#mid

Use it like so:

像这样使用它：

Debug.Print Mid("abcde", InStr("abcde", "c"))

Will return "cde"

将返回“cde”

I used Instr to locate where it should start from instead of writing a number. To get it after the comma, use:

我使用 Instr 来定位它应该从哪里开始而不是写一个数字。要在逗号后获取它，请使用：

Debug.Print Mid(Mystring, InStr(Mystring, "," + 1))

Where Mystring would be your string. I wrote +1 so it starts after the comma.

其中 Mystring 将是您的字符串。我写了 +1 所以它在逗号之后开始。