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.
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
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
' 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
The Excel Formula below is an array formula that will sort a column and bring the contents to the top of the list. It’s particularly useful if you have blanks in your data that’s intermittently placed throughout a column. If you want the data to display at the bottom (instead of the top of the list), just swap out the word “LARGE” below with “SMALL”. Enter the formula and drag down adjacent to the list that you want evaluated. Remember to press SHIFT + CTRL + ENTER on the first cell to make it an array formula…then drag down from there.