Thanks to my buddy Smitty for help on this one...
The VBA Module below will search a column of data (in this case: Col G, Row 2 down) and take a cell's contents and convert it to a string. It will then search the String for 2 key words (specified in the code below) and return the text value that sits 'in between'. If the code encounters a duplicate within the cell (aka: string), it will ignore it and only return the unique value(s) between the 2 key words. With this specific routine, it finds the instance(s) of "Component: " and "; Outcome" and counts backwards 11 spaces to return the value in between. When re-using this code, please remember to modify the "Range" values, Component / Outcome key words (in quotes), and finally the # of characters with which to Start and End the parsed value.
Sub GetValues()
Dim rngCell As Range
Dim strName As String
Dim Component As Integer
Dim Outcome As Integer
Dim CompleteResult As String
Dim CurrentResult As String
Dim ComponentCnt As Integer
Dim RowCnt As Integer
RowCnt = Range("G1").End(xlDown).Row
Range("H2:H" + Trim(str(RowCnt - 1))).Select
Selection.ClearContents
For Each rngCell In Range("G2", Range("G1").End(xlDown))
CompleteResult = ""
strName = rngCell.Value
' Change the keyword values below to accomodate your "in between" criteria.
Component = InStr(1, strName, "Component: ")
Outcome = InStr(1, strName, "; Outcome")
While Component > 0
If CompleteResult <> "" Then
CompleteResult = CompleteResult + vbLf
End If
' Prevent Duplicates
' The number values in the line immediately following = spaces left and right to start the string start and finish points.
CurrentResult = Mid(strName, Component + 11, Outcome - Component - 11)
If InStr(CompleteResult, CurrentResult) = 0 Then
CompleteResult = CompleteResult + CurrentResult
End If
Component = InStr(Outcome + 1, strName, "Component: ")
Outcome = InStr(Outcome + 1, strName, "; Outcome")
Wend
rngCell.Offset(0, 1).Value = CompleteResult
Next rngCell
MsgBox ("DONE")
End Sub
