Obtain data from a cell with lots of paragraphs

HansVogelaar 

Thank you so much for the function! you're a god.

however, I need to tweak it a bit.

so the UC, was for the student Amy.

when Hamlet used the word "my" in his sentence, I needed to know what Horatio said.

not what were the words before "my" in the same sentence hamlet uttered.

For those where "my" is in the sentence the very first person said. Since there is no previous person, it can return a blank.

If possible, to make the output like ": Where, my lord?HAMLET:"

i.e, Searching from the nearest ":" to the next nearest preceding ":".

or ideally HORATIO: Where, my lord? (but given how dynamic the ideal output is, just Searching from the nearest ":" to the next nearest preceding ":". as stated above is more than enough.

What changes do I need to make in this code you've provided. (also, how to get this copy of this function permanently on all my workbooks?) TYSM! you're so awesome at this!

code

Option Explicit

Function ExtractParts(Txt As String, Keyword As String) As String
Dim strLines() As String
Dim varLine As Variant
Dim i As Long
Dim p1 As Long
Dim p2 As Long
Dim n As Long
Dim strReturn() As String
strLines = Split(Txt, vbLf)
For Each varLine In strLines
p2 = InStr(1, varLine, Keyword, vbTextCompare)
If p2 > 0 Then
p1 = InStr(1, varLine, ":")
n = n + 1
ReDim Preserve strReturn(1 To n)
strReturn(n) = Trim(Mid(varLine, p1 + 1, p2 - p1 - 1))
End If
Next varLine
ExtractParts = Join(strReturn, vbLf)
End Function

 

 

---