Friday, August 30, 2013

Excel - Search Cell for Matching Value

Excel Formula to search a cell's contents for the value within quotes and determine if it contains a match.  If True, it returns the cell's contents.  If False, it leaves it blank.


Monday, August 19, 2013

Parse Cell String Contents (VBA)

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

    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")
        rngCell.Offset(0, 1).Value = CompleteResult
    Next rngCell

    MsgBox ("DONE")
End Sub