extract a text after searching for a specific name

[swofm]

Hello everyone,
I have this excel spreadsheet and one of the column contains notes. I want to be able to extract a specific text based on a common user.

Below is an example of what is on the notes column. These are all on one cell.

Test,Name1 10/18/2017 8:43:15 AM > FAXED
Test,Name2 11/1/2017 1:49:30 PM > this is for testing purposes only.
Test,Name1 2/20/2018 12:40:30 PM > this is for more testing.
Test,Name1 2/26/2018 10:26:51 AM > testing attached

Say if i want to extract the following: "Test,Name1 10/18/2017", "Test,Name1 2/20/2018", "Test,Name1 2/26/2018" from the notes, I need help on making the formula to extract them and save them on a new sheet. Any help is very much appreciated.

Thank you,
Jason

 

[Roderick_E]

stringpart = Trim(Left(strunbound(i), InStr(strunbound(i), ">") - 1))

Ah-ha so one of your notes cells doesn't contain ">"

This version of my code checks the notes cell for the presence of ">" and does nothing if not there.

Sub testit()
Dim strarray As String
Dim strunbound() As String
Dim i As Long
strarray = Range("A1") 'or any string with unique dilimiter
strunbound = Split(strarray, Chr(10)) 'replace vbCrLf with any unique delimiter such as ","
For i = LBound(strunbound) To UBound(strunbound)
If InStr(strunbound(i), ">") > 0 Then
stringpart = Trim(Left(strunbound(i), InStr(strunbound(i), ">") - 1))
If stringpart Like "*" & "Name1" & "*" Then
MsgBox stringpart
End If
End If
Next i
End Sub

 

[swofm]

Hi, so the "Name2" i replace it with the name i'm looking for? So when I do that it finds the first instance but throws the same error on "stringpart = Trim(Left(strunbound(i), InStr(strunbound(i), ">") - 1))" when I click Debug. Thank you.

 

[swofm]

Hi, this last code is working now, so probably the next question is how do I run this to search the whole worksheet and populate the patient id and name that are on columns A and B. Example is say patient A has notes with 3 instances of the name I'm searching for. so the output will be on 3 rows:
idA patientA Name1 datetime_1
idA patientA Name1 datetime_2
idA patientA Name1 datetime_3

 

[Roderick_E]

Hi, this last code is working now, so probably the next question is how do I run this to search the whole worksheet and populate the patient id and name that are on columns A and B. Example is say patient A has notes with 3 instances of the name I'm searching for. so the output will be on 3 rows:
idA patientA Name1 datetime_1
idA patientA Name1 datetime_2
idA patientA Name1 datetime_3

Can we concatenate into one column with comma separation?

idA patientA Name1 datetime_1,idA patientA Name1 datetime_2,idA patientA Name1 datetime_3

Or put the results on a newly created sheet as multiple rows as you want?

 

[Roderick_E]

This should create a new sheet and put all the results on it.

Sub testit()
Dim strarray As String
Dim strunbound() As String
Dim i As Long
dim cnt as long
dim newsheetname as string
dim patname as string
patname = inputbox("Input a patient name to search")
cnt = 2
newsheetname = application.text(now(),"YYMMDDHHmmss")
With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = newsheetname
    End With
strarray = Range("A1") 'or any string with unique dilimiter
strunbound = Split(strarray, Chr(10)) 'replace vbCrLf with any unique delimiter such as ","
For i = LBound(strunbound) To UBound(strunbound)
If InStr(strunbound(i), ">") > 0 Then
stringpart = Trim(Left(strunbound(i), InStr(strunbound(i), ">") - 1))
If stringpart Like "*" & patname & "*" Then
sheets(newsheetname).cells(cnt,1) = stringpart
cnt = cnt + 1
End If
End If
Next i
msgbox "See new tab for results: " & newsheetname
sheets(newsheetname).activate
End Sub

 

[swofm]

if you concatenate into one column, would that be easy to separate too? If you don't mind, it would be ideal on a new sheet as multiple rows. I really appreciate your patience here. Thank you.

 

[Roderick_E]

No worries, see my reply above. I even added a prompt box where you can type in the patient name. Please test.

 

[Roderick_E]

ugh I already see some issues, one moment. I'll post new code in a sec...

 

[swofm]

my file has multiple patients (4,185 rows). patientid on column A and patientname on column B, the notes are on column P. so i tried to run it, it created a new sheet but it was blank. Thank you

 

[Roderick_E]

Modifications in red

[COLOR=#ff0000][/COLOR]Sub testit()
Dim strarray As String
Dim strunbound() As String
Dim i As Long
Dim cnt As Long
Dim newsheetname As String
Dim patname As String
[COLOR=#ff0000]Dim ssheet As String[/COLOR]
[COLOR=#ff0000]ssheet = activesheetname[/COLOR]
patname = InputBox("Input a patient name to search")
cnt = 2
newsheetname = Application.Text(Now(), "YYMMDDHHmmss")
With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = newsheetname
    End With
    
strarray = [COLOR=#ff0000]Sheets(ssheet).[/COLOR]Range("A1") 'or any string with unique dilimiter
strunbound = Split(strarray, Chr(10)) 'replace vbCrLf with any unique delimiter such as ","
For i = LBound(strunbound) To UBound(strunbound)
If InStr(strunbound(i), ">") > 0 Then
stringpart = Trim(Left(strunbound(i), InStr(strunbound(i), ">") - 1))
If stringpart Like "*" & patname & "*" Then
Sheets(newsheetname).Cells(cnt, 1) = stringpart
cnt = cnt + 1
End If
End If
Next i
MsgBox "See new tab for results: " & newsheetname
Sheets(newsheetname).Activate
End Sub