How to return only text after a comma in a string

Question

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)

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.

What am I doing wrong?

Answer 1

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:

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