Geek Answers Handbook

How to return only text after a comma in a string

I am extracting text between parens in the active window title bar. This part works great (thanks to some help I got here earlier!). 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 on the left (HENDERSON, TOM) some text on the right (there is no space after the comma)

The last macro works fine. 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 or last line of code has a “right” and not a “left” command:

 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, not just the first name.

The only examples I can find anywhere on Google are specifically for Excel. I have combined my VBA guides and they all give similar examples that I used to extract the text to the right of the character.

What am I doing wrong?

+6
source share
3 answers

You can use Split() to create an array of comma-separated parts of the text, then access 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
+7
source

It seems like everyone clicked on Split to get the first / second part of the name. You can also use Split to get rid of parentheses. This works if you know that you will (and always will) have only one ( ) .

The code gives the main idea. You can use Split to get a part of a String that doesn't include ( or ) , and then do it again to get both sides,.

 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 two Split and three Split options. The main difference is that the three varieties of Split use the same code to remove the brackets, and the only change is which side to capture. Two variants of Split use the fact that comma-delimited deletes one of the brackets for free. Indexes there are a bit more complicated.

+3
source

EDIT, it seems I did not understand, I understood well for the first time. Attempt 2:

You want to remove all permissions from the comma.

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

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

You are looking for the Mid function.

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

Use it like this:

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

Will return "cde"

I used Instr to find where it should start, instead of writing a number. To get it after the decimal point, use:

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

Where Mystring will be your string. I wrote +1 so that it starts after the decimal point.

+2
source

More articles:


All Articles