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]

Are the notes linebreaks? carriage returns? The constant is the ">". Would it be ok to keep the time in the result?

 

[swofm]

their carriage returns. time is ok to keep.
Thank you.

 

[Roderick_E]

This snippet of code could put you on right path.
To test,add this macro to your workbook and change "A1" to one of the cells with your notes.
This will simply show you a popup message of each note entry in the cell, stripping everything after ">" then show the next line.
Once that is accomplished we can work to put the data on another sheet.

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)
MsgBox Trim(Left(strunbound(i), InStr(strunbound(i), ">") - 1))
Next i
End Sub

 

[swofm]

sorry i haven't mentioned, if you notice, there are multiple users on the notes, what if i want to extract Test,Name1 only?
Thank you.

 

[Roderick_E]

We can still cycle through and only capture the name you want... See conditions in red. whatevername will be replaced by the name your want matched within the stringpart (note).

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)



[COLOR=#ff0000]stringpart = Trim(Left(strunbound(i), InStr(strunbound(i), ">") - 1))
if "*" & whatevername & "*" like stringpart then
msgbox stringpart
endif[/COLOR]



Next i
End Sub

 

[swofm]

so i ran the macro but it's giving me "Run-time error '5'. Invalid procedure call or argument".

 

[Roderick_E]

so i ran the macro but it's giving me "Run-time error '5'. Invalid procedure call or argument".

Please Debug and tell me which line highlights -- thanks

 

[Roderick_E]

I had the LIKE backward, sorry about that:

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)
stringpart = Trim(Left(strunbound(i), InStr(strunbound(i), ">") - 1))
If stringpart Like "*" & "Name2" & "*" Then
MsgBox stringpart
End If
Next i
End Sub

 

[swofm]

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